How to declare variables in PL/pgSQL stored procedures

The title of this post makes use of 3 terms: PL/pgSQL, stored procedure, and variable. Let’s start with a basic understanding of them.

PL/pgSQL: An abbreviation for Procedure Language/PostgreSQL. It is a procedural language that provides the ability to perform more complex operations and computations than SQL.

Stored Procedure: A block for SQL statements combined together under a name and saved in database which can be called on multiple times when needed.

Variable: A variable holds a value that can be changed through the block. It is always associated with a datatype. 

Now let’s try to understand these with examples.

Stored procedures include functions, procedures, triggers, and other objects that can be saved in databases. Below is a simple example for a stored procedure “Procedure”:

postgres=# CREATE PROCEDURE example1 () AS $$

postgres$# BEGIN

postgres$# RAISE NOTICE 'Procedure example1 called';

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example1();ee

NOTICE:  Procedure example1 called

CALL

In this example, an SQL statement, which upon call prints “Procedure example1 called,” is saved under the name example1 and can be called multiple times as needed.

The example has a fixed message which it prints upon call. To make the function more dynamic and useful, we can use different types of variables and assign values to them at compile time as well at run time.

A variable must be declared in the declaration section of the PL/pgSQL block. Declaration syntax for a variable is: “variable_name data_type [:=value/constant/expression];”

Variable_name: This can be any meaningful name or whatever the user wants.

Data_type: PostgreSQL supports data types like integer, numeric, varchar, and text, or it can be a %TYPE or %ROWTYPE. Here is a list of PostgreSQL supported data types: https://www.postgresql.org/docs/current/datatype.html.

Variable Assignment: Any value as accepted by data type, constant, or expression can be assigned to the variable. This part is optional. 

The user can print variable values by using RAISE NOTICE/EXCEPTION and “%” as a placeholder to be replaced by the variable value.

Let’s see an example for variable declaration and display:

postgres=# CREATE PROCEDURE example2 () AS $$

postgres$# DECLARE

postgres$# var1_int INTEGER := 10;

postgres$# var2_text TEXT := 'this is text type variable';

postgres$# var3_date DATE := now();

postgres$# BEGIN

postgres$# RAISE NOTICE 'variable 1 var1_int value is : %', var1_int; 

postgres$# RAISE NOTICE 'variable 2 var2_text value is : %', var2_text;

postgres$# RAISE NOTICE 'variable 3 var3_date value is : %', var3_date;

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example2 ();

NOTICE:  variable 1 var1_int value is : 10

NOTICE:  variable 2 var2_text value is : this is text type variable

NOTICE:  variable 3 var3_date value is : 2019-11-21

CALL

The variable can also be of a column type or a row type of a table. These can be declared with data type as %TYPE and %ROWTYPE. Here is an example:

postgres=# CREATE TABLE emp (eid INTEGER, name TEXT);

CREATE TABLE

postgres=# INSERT INTO emp VALUES (1,'emp1'), (2, 'emp2');

INSERT 0 2

postgres=# CREATE PROCEDURE example3 () AS $$

postgres$# DECLARE

postgres$# eid_var emp.eid%TYPE; 

postgres$# result emp%ROWTYPE;

postgres$# BEGIN

postgres$# eid_var := 2;

postgres$# SELECT * INTO result FROM emp WHERE eid = eid_var;

postgres$# RAISE NOTICE 'emp details for emp 2 is %', result;

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example3 ();

NOTICE:  emp details for emp 2 is (2,emp2)

CALL

In this example the data type of the variable “eid_var” is declared by reference to the “eid” column’s data type in the “emp” table As output the user wants to return a complete row (all columns) of the “emp” table, so the variable “result” is declared as a reference to a whole row type of the “emp” table.

Another point to notice is that the “result” variable is assigned at runtime by using the result set of SELECT * INTO.

Another way to use %ROWTYPE in PostgreSQL variables is using RECORD as the data type of a variable. Below is the same example as above, but displaying “emp” table data using RECORD type.

postgres=# CREATE PROCEDURE example4 () AS $$

postgres$# DECLARE

postgres$# eid_var emp.eid%TYPE; 

postgres$# result RECORD;

postgres$# BEGIN

postgres$# eid_var := 2;

postgres$# SELECT * INTO result FROM emp WHERE eid = eid_var;

postgres$# RAISE NOTICE 'emp details for emp 2 is %', result;

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example4 ();

NOTICE:  emp details for emp 2 is (2,emp2)

CALL

In the same way, the user can use variables in other stored procedures like function and triggers.

Reference Links:

https://www.postgresql.org/docs/current/datatype.html

https://www.postgresql.org/docs/current/plpgsql-declarations.html

https://www.postgresql.org/docs/current/sql-createprocedure.html

​