PostgreSQL: Sharing data across function calls

Recently I did some PostgreSQL consulting in the Berlin area (Germany) when I stumbled over an interesting request: How can data be shared across function calls in PostgreSQL? I recalled some of the older features of PostgreSQL (15+ years old or so) to solve the issue. Here is how it works.

Stored procedures in PostgreSQL

As many of you might know, PostgreSQL allows you to write stored procedures in many different languages. Two of the more popular ones are Perl and Python, which have been around for quite some time. The cool thing is: Both languages offer a way to share variables across function calls. In Perl you can make use of the $_SHARED variable, which is always there.

Here is an example:

CREATE OR REPLACE FUNCTION set_var(int)
RETURNS int AS $$
   $_SHARED{'some_name'} = $_[0];
   return $_[0];
$$ LANGUAGE plperl;

What the code does, is to assign a value to some_name and return the assigned value. Some other function can then make use of this data, which is stored inside your database connection. Here is an example:

CREATE OR REPLACE FUNCTION increment_var()
RETURNS int AS $$
   $_SHARED{'some_name'} += 1;
   return $_SHARED{'some_name'};
$$ LANGUAGE plperl;

This function will simply increment the value and return it. As you can see the code is pretty simple and easy to write.

Assigning shared variables

The following listing shows, how the code can be used. The first call will assign a value to the function while the second one will simply increment that value:

test=# SELECT set_var(5);
 set_var
---------
 5
(1 row)

test=# SELECT increment_var(), increment_var();
 increment_var | increment_var
---------------+---------------
             6 | 7
(1 row)

It is especially noteworthy here that the second column will already see the changes made by the first column, which is exactly what we want here.

Shared variables and transactions

When working with shared variables in PL/Perl or PL/Python you have to keep in mind that those changes will not be transactional as all the rest in PostgreSQL is. Even if you rollback a transaction you can observe that those values will stay incremented:

test=# BEGIN;
BEGIN
test=# SELECT increment_var(), increment_var();
increment_var  | increment_var
---------------+---------------
             8 | 9
(1 row)
test=# ROLLBACK;
ROLLBACK
test=# SELECT increment_var(), increment_var();
 increment_var | increment_var
---------------+---------------
            10 | 11
(1 row)

This behavior makes shared values actually a nice thing to have if you want to preserve data across transactions.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.