PostgreSQL Variables

Introduction To PostgreSQL Variables

The PostgreSQL variable is a name of a memory location that is used to store the data temporarily at the time of function execution. They have a particular data type give to the following such as text, char, date, time, Boolean, etc. The PostgreSQL variables are not defined with the default value, it can be initialized to the NULL value. By using a function or code block, we can alter the value stored in the PostgreSQL variable.

Uses Of Variables

The uses of variables in PostgreSQL are listed as follows:

• They are used to implement the particular function it can be defined within the function under the DECLARE keyword.
• When the time of function execution, we can store the data temporarily in the PostgreSQL variable.
• It allows that to modify the data that can be stored within the variable.
• It does not allow that to change the value of data when it is defined as CONSTANT.
• PostgreSQL is not defined as the CONSTANT, then we can declare with a default value and we can modify it later.

How To Initialize The Variables?

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

Example:

Code:

DECLARE num_of_students integer := 200;

• The above example is used to declare the PostgreSQL variable. The initial value of the variable is 200. The name of the variable is the number of students and the datatype is an integer.
• We can initialize the variable only after the declaration of the variable.

Code:

DECLARE num_of_students integer;

The statement is used to declare the PostgreSQL variable only.

From the above example:

Code:

num_of_students:=200

The statement is used to initialize a PostgreSQL variable of the name.

How To Declare Variables?

Declaration of Variable is can be understood by the following statement.

Code:

variable_name data_type [:= expression];

• The first step is to give the specific name to a variable like an index or counter.
• The second step is to define the data type of variables like INTEGER, NUMERIC, VARCHAR, and CHAR.
• The third step is giving the default value to the variable. If you don’t know the value then the value is initialized in NULL.

Examples of PostgreSQL Variables

Given below are the examples mentioned:

Example #1

Code for without DEFAULT Keyword.

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$;

SQL Statement:

Code:

SELECT public."Initial_val_without_default_keyword"()

Output:

Example #2

Codes for with default keyword.

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$;

SQL Statement:

Code:

SELECT public."Initial_val_with_default_keyword"()

Output:

Example #3

Code for constant variable without DEFAULT Keyword.

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$;

SQL Statement:

Code:

SELECT public."Initial_val_constant_without_default_keyword"()

Output:

Example #4

Code for constant variable with the default keyword.

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$;

SQL Statement:

Code:

SELECT public."Initial_val_constant_with_default_keyword"()

Output:

Example #5

Code which is gives the value to a variable after the declaration.

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$;

SQL Statement:

Code:

SELECT public."Initial_val_later"()

Output:

Conclusion

This article explains the PostgreSQL variables, initialize and declaration of variables and its uses.

Recommended Articles

This is a guide to PostgreSQL Variables. Here we discuss the introduction, how to declare and initialize variables along with its example with code implementation. You can also go through our other suggested articles to learn more –

1. What is PostgreSQL?
2. How to Install PostgreSQL?
3. PostgreSQL Versions
4. PostgreSQL Features