PostgreSQL Variables

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 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 then we can not change the value of the variable 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 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 value as 300.

How to Declare Variables in PostgreSQL?

There are various ways to declare the variable that are 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 are using in the function needs to be defined within that function under the DECLARE keyword.
• We can store the data temporarily in the variable during the function execution.
• We can modify the data stored within the variable.
• If any if the PostgreSQL variable is defined as the CONSTANT then we can not change the value stored within the variable.
• We can declare a PostgreSQL variable with a default value or we can modify it later on as per need if it is not defined CONSTANT.

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 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 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 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 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 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 snapshot of the above function:

Code:

SELECT public."Initial_val_later"()

Output:

Recommended Articles