Will PostgreSQL14 finally come with schema variables?

One of the bits you need to solve when you migrate from Oracle to PostgreSQL is this: In the Oracle database there are PL/SQL packages and some of those have package variables defined. PostgreSQL does not know the concept of a package but you can use schemas to group your PL/pgSQL functions and procedures. When it comes to packages variables there is no easy solution currently.

Tip: You can add your own parameters in #postgresql pic.twitter.com/sdaOMStqqU

— Daniel Westermann (@westermanndanie) August 7, 2020

to emulate that, but it is nasty and not the intended use case for this. Schema variables are under discussion for quite some time and the good news is, that the patch is now “Ready for Committer”. This still is not a guarantee that PostgreSQL 14 will come with schema variables, but at least it is close to that.

As always, lets do some simple demos to understand what is going on and how the feature might help, once it is committed. The most simple example for a schema variable is this:

postgres=# create variable var1 as int;
CREATE VARIABLE

As the name of the feature (schema variables) implies, a variable is created in a specific schema. As I have not modified the default search_path the variable got created in the public schema. You can easily check this in the new catalog table pg_variable:

postgres=# select varname, varnamespace::regnamespace from pg_variable;
 varname | varnamespace 
---------+--------------
 var1    | public
(1 row)

By default, once a variable is created, it is persistent and available again after the instance is restarted:

postgres=# ! pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-19 01:29:48.825 CET - 1 - 80179 -  - @ LOG:  redirecting log output to logging collector process
2020-11-19 01:29:48.825 CET - 2 - 80179 -  - @ HINT:  Future log output will appear in directory "pg_log".
 done
server started
postgres=# select 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# select var1;
 var1 
------
     
(1 row)

Until now the variable does not contain any value, as we did not assign anything. To do that you can use “let”:

postgres=# h let
Command:     LET
Description: change a schema variable's value
Syntax:
LET schema_variable = sql_expression
LET schema_variable = DEFAULT

URL: https://www.postgresql.org/docs/devel/sql-let.html

Assigning a value can be as simple as this:

postgres=# let var1 = 1;
LET
postgres=# select var1;
 var1 
------
    1
(1 row)

.. or you can calculate the new value:

postgres=# let var1 = var1 * 2;
LET
postgres=# select var1;
 var1 
------
    2
(1 row)

The value, a variable has assigned to, is not persistent, it lives only for the duration of the session:

postgres=# ! pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-19 01:44:42.837 CET - 1 - 80305 -  - @ LOG:  redirecting log output to logging collector process
2020-11-19 01:44:42.837 CET - 2 - 80305 -  - @ HINT:  Future log output will appear in directory "pg_log".
 done
server started
postgres=# select 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# select var1;
 var1 
------
     
(1 row)

If you want to have the value of a variable persistent you need to make it immutable:

postgres=# create immutable variable var2 as int default 2;
CREATE VARIABLE
postgres=# select var2;
 var2 
------
    2
(1 row)

postgres=# ! pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-19 01:58:53.365 CET - 1 - 80414 -  - @ LOG:  redirecting log output to logging collector process
2020-11-19 01:58:53.365 CET - 2 - 80414 -  - @ HINT:  Future log output will appear in directory "pg_log".
 done
server started
postgres=# select 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# select var2;
 var2 
------
    2
(1 row)

Important to understand is, that variables are not transaction safe by default:

postgres=# create variable var3 as int default 3;
CREATE VARIABLE 
postgres=# select var3;
 var3 
------
    3
(1 row)

postgres=# begin;
BEGIN
postgres=*# let var3=4;
LET
postgres=*# rollback;
ROLLBACK
postgres=# select var3;
 var3 
------
    4
(1 row)

But you can do it, if you want:

postgres=# create variable var4 as int default 5 on transaction end reset;
CREATE VARIABLE
postgres=# begin;
BEGIN
postgres=*# let var4 = 10;
LET
postgres=*# select var4;
 var4 
------
   10
(1 row)

postgres=*# rollback;
ROLLBACK
postgres=# select var4;
 var4 
------
    5
(1 row)

Like tables, variables can also be temporary:

postgres=# create temporary variable var6 as int default -1;
CREATE VARIABLE
postgres=# ! pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-11-19 02:22:22.308 CET - 1 - 80611 -  - @ LOG:  redirecting log output to logging collector process
2020-11-19 02:22:22.308 CET - 2 - 80611 -  - @ HINT:  Future log output will appear in directory "pg_log".
 done
server started
postgres=# select 1;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# select var6;
ERROR:  column "var6" does not exist
LINE 1: select var6;
               ^
postgres=# 

… and you can also specify to drop the variable at commit time:

postgres=# begin;
BEGIN
postgres=*# create temporary variable var7 as int default -1 on commit drop;
CREATE VARIABLE
postgres=*# let var7 = -9;
LET
postgres=*# commit;
COMMIT
postgres=# select var7;
ERROR:  column "var7" does not exist
LINE 1: select var7;
               ^
postgres=# 

Variables can be referenced in procedures, functions and in SQL:

postgres=# create variable var8 as int default 100;
CREATE VARIABLE
postgres=# create variable var8 as int default 100;
CREATE VARIABLE
postgres=# create function f1() returns int as $$select var8;$$ language SQL;
CREATE FUNCTION
postgres=# select f1();
 f1  
-----
 100
(1 row)

postgres=# create procedure p1() as $$
postgres$# declare
postgres$# begin
postgres$#   let var8 = 101;
postgres$#   raise notice '%', var8;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE PROCEDURE
postgres=# call p1();
NOTICE:  101
CALL

postgres=# create table t1 ( a int, b text );
CREATE TABLE
postgres=# select var8;
 var8 
------
  101
(1 row)

postgres=# insert into t1 values (101,'aaa');
INSERT 0 1
postgres=# select * from t1 where a = var8;
  a  |  b  
-----+-----
 101 | aaa
(1 row)

This is really a great feature and I do hope it finally gets committed for PostgreSQL 14.