Using custom variables as per-session global variables
In a thread in the italian mailing list we were discussing about session global variables, something I believe is a bad idea, no matter what is the problem you are trying to solve, but probably a more database-oriented approach could solve it (e.g., temporary tables).One thing I did not know, and I discovered thanks to the above discussion (credits to Andrea Adami) is that PostgreSQL allows the definition of custom variables by means of
SET. Well, SET is of course the way to configure a GUC, that is a configuration parameter of the cluster.
As you probably know, all GUCs that have a name without a namespace are cluster-wide, while those with a prefix belong to an extension.
Since PostgreSQL does not know in advance if an extension has been loaded or not, and since extension can be loaded at run-time, the cluster allows the user to set parameters that contain a prefix in the name. Documentation can be found here. Therefore, it is possible to use
SET to define a fake GUC variable to be used in queries and functions.
As an example:
testdb=> SET fluca1978.favourite_database TO 'PostgreSQL';
SET
testdb=> SHOW fluca1978.favourite_database;
fluca1978.favourite_database
------------------------------
PostgreSQL
(1 row)
testdb=> SELECT 'Luca loves ' || current_setting( 'fluca1978.favourite_database' );
?column?
-----------------------
Luca loves PostgreSQL
(1 row)
The variable behaves as a
user context parameter, and honor also transaction boundaries:
estdb=> SELECT 'Luca loves ' || current_setting( 'fluca1978.favourite_database' );
?column?
-----------------------
Luca loves PostgreSQL
(1 row)
testdb=> BEGIN;
BEGIN
testdb=*> SET fluca1978.favourite_database TO 'Oracle';
SET
testdb=*> SELECT 'Luca loves ' || current_setting( 'fluca1978.favourite_database' );
?column?
-------------------
Luca loves Oracle
(1 row)
-- argh!
-- rollback!
testdb=*> ROLLBACK;
ROLLBACK
testdb=> SELECT 'Luca loves ' || current_setting( 'fluca1978.favourite_database' );
?column?
-----------------------
Luca loves PostgreSQL
(1 row)
Clearly, this kind of variable is session-scoped and cannot be shared among different sessions:
testdb=> SELECT pg_backend_pid(), current_setting( 'fluca1978.favourite_database' );
pg_backend_pid | current_setting
----------------+-----------------
857 | PostgreSQL
(1 row)
-- in another session
testdb=> SELECT pg_backend_pid(), current_setting( 'fluca1978.favourite_database' );
ERROR: unrecognized configuration parameter "fluca1978.favourite_database"