SUMMARY: This article covers how stored procedures can make use of variables to be more functional and useful. After defining PL/pgSQL, stored procedures, and variables, it provides examples of how variables can be used.
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