PostgreSQL Variables

Updated May 12, 2023

Introduction to PostgreSQL Variables

The PostgreSQL variable is a convenient name or an abstract name given to the memory location. The variable always has a particular data-type give to it, like boolean, text, char, integer, double precision, date, time, etc. They are used to store the data which can be changed. The PostgreSQL variables are initialized to the NULL value if they are not defined with a DEFAULT value. We can modify the value stored within the variable by using the function or code block. We can store the data temporarily in the variable during the function execution.

Syntax:

Consider the following syntax to declare a variable:

DECLARE var_name [ CONSTANT ] data-type [ NOT NULL ] [ { DEFAULT | := } initial_value ]

Explanation:

• var_name: The variable name to assign.
• CONSTANT: This is an optional component. If we have defined the CONSTANT, we can not change the variable’s value once the variable has been initialized.
• data-type: The variable data-type to assign.
• NOT NULL: This is an optional component. If we have defined the NOT NULL, then the variable can not have a NULL value.
• initial_value: This is an optional component. By using this, we can initialize the variable while creating the variable. If we have not defined the initial_value, then the variable will be assigned with the NULL value.

How to Initialize Variables in PostgreSQL?

There are various ways to initialize the variables that are given as follows:

1. While the creation

We can initialize the variable while creating the variable by giving an initial value.

Consider the following example to understand the variable initialization.

Code:

DECLARE num_of_students integer := 100;

or

DECLARE num_of_students integer DEFAULT 100;

The above example would declare a PostgreSQL variable of name num_of_students having initial_value as 100 and data-type as an integer.

2. After creation

We can declare a variable first, and then we can initialize the variable.

Consider the following example to understand the variable initialization after creation.

Code:

DECLARE num_of_students integer;

The above example would declare a PostgreSQL variable of name num_of_students having data-type as an integer.

Now we will initialize the variable by using the following statement:

Code:

num_of_students := 300

The above statement would initialize a PostgreSQL variable of name num_of_students with a value of 300.

How to Declare Variables in PostgreSQL?

There are various ways to declare the variable that is given as follows:

1. DECLARE with initial_value

Consider the following example to understand the variable declaration with initial_value.

Code:

DECLARE name_of_student VARCHAR:= 'John';

or

DECLARE name_of_student VARCHAR DEFAULT 'John';

The above example would declare a PostgreSQL variable of name name_of_student having data-type as VARCHAR and initial_value as ‘John’.

2. DECLARE without initial_value

Consider the following example to understand the variable declaration without an initial value.

Code:

DECLARE name_of_student VARCHAR;

The above example would declare a PostgreSQL variable of name name_of_student having data-type as VARCHAR.

3. DECLARE CONSTANT variable

Consider the following example to understand the variable declaration with an initial value and as a CONSTANT.

Code:

DECLARE name_of_student CONSTANT VARCHAR:= 'John';

or

DECLARE name_of_student CONSTANT VARCHAR DEFAULT 'John';

The above example would declare a PostgreSQL variable of name name_of_student having data-type as VARCHAR and having an initial value as ‘John’, which will be changed further as it is specified as CONSTANT.

How do Variables work?

• All of the PostgreSQL variables we use in the function must be defined within the DECLARE keyword.
• During the execution of the function, we can temporarily store the data in the variable.
• We can modify the data stored within the variable.
• We cannot change the variable’s value if any of the PostgreSQL variables is defined as the CONSTANT.
• If a PostgreSQL variable is not specified as CONSTANT, we can declare it with a default value and change it later as necessary.

Examples of PostgreSQL Variables

Given below are the examples:

Example #1

Gives initial value to a PostgreSQL variable.

a. Without DEFAULT keyword

Consider the following function of the name:

Code:

/*"Initial_val_without_default_keyword"()*/
-- FUNCTION: public."Initial_val_without_default_keyword"()
-- DROP FUNCTION public."Initial_val_without_default_keyword"();
CREATE OR REPLACE FUNCTION public."Initial_val_without_default_keyword"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
def_var integer := 100;
BEGIN
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and snapshot the result of the above function.

Code:

SELECT public."Initial_val_without_default_keyword"()

Output:

b. With default keyword

Consider the following function of the name:

Code:

"Initial_val_with_default_keyword"()
-- FUNCTION: public."Initial_val_with_default_keyword"()
-- DROP FUNCTION public."Initial_val_with_default_keyword"();
CREATE OR REPLACE FUNCTION public."Initial_val_with_default_keyword"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
def_var integer default 200;
BEGIN
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and snapshot the result of the above function.

Code:

SELECT public."Initial_val_with_default_keyword"()

Output:

c. CONSTANT variable

• without DEFAULT keyword

Consider the following function of the name:

Code:

-- FUNCTION:
public."Initial_val_constant_without_default_keyword"()
-- DROP FUNCTION
public."Initial_val_constant_without_default_keyword"();
CREATE OR REPLACE FUNCTION
public."Initial_val_constant_without_default_keyword"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$ DECLARE
def_var CONSTANT integer := 200;
BEGIN
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and snapshot the result of the above function.

Code:

SELECT public."Initial_val_constant_without_default_keyword"()

Output:

• With default keyword

Consider the following function of the name:

Code:

-- FUNCTION:
public."Initial_val_constant_with_default_keyword"()
-- DROP FUNCTION
public."Initial_val_constant_with_default_keyword"();
CREATE OR REPLACE FUNCTION
public."Initial_val_constant_with_default_keyword"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
def_var CONSTANT integer default 300;
BEGIN
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and snapshot to understand the result of the above function:

Code:

SELECT public."Initial_val_constant_with_default_keyword"()

Output:

Example #2

Gives a value to a PostgreSQL variable after declaration.

Consider the following function of the name:

Code:

-- FUNCTION: public."Initial_val_later"()
-- DROP FUNCTION public."Initial_val_later"();
CREATE OR REPLACE FUNCTION public."Initial_val_later"(
)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$Declare
def_var integer ;
BEGIN
def_var:=500;
RETURN def_var;
END;
$BODY$;

Now we will execute the above function.

Illustrate the following SQL statement and a snapshot of the above function:

Code:

SELECT public."Initial_val_later"()

Output:

Recommended Articles