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.
Table of Contents
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 |
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
+43 (0) 2622 93022-0
office@cybertec.at
As one of the most popular languages for function bodies is PL/PgSQL, what about it?
For PL/PgSQL we are using something like this:
CREATE FUNCTION globals_set_variable(p_variable character varying, p_value character varying)
RETURNS character varying AS
$BODY$
BEGIN
PERFORM set_config('globals.' || p_variable, p_value, false);
RETURN p_value;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE FUNCTION globals_get_variable(p_variable character varying)
RETURNS character varying AS
$BODY$
DECLARE
v_variable character varying;
BEGIN
SELECT NULLIF(current_setting('globals.' || p_variable), '') INTO v_variable;
RETURN v_variable;
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;