Session based global variable in Postgresql stored procedure?

Question

In Oracle's PL/SQL I can create a session based global variable with the package definition. With Postgresql's PLpg/SQL, it doesn't seem possible since there are no packages, only independent procedures and functions.

Here is the syntax for PL/SQL to declare g_spool_key as a global...

CREATE OR REPLACE PACKAGE tox IS
     g_spool_key spool.key%TYPE := NULL;
     TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE;
     PROCEDURE begin_spool;
     PROCEDURE into_spool
      (
      in_txt IN spool.txt%TYPE
      );
     PROCEDURE reset_spool;
     FUNCTION end_spool
      RETURN t_spool;
     FUNCTION timestamp
      RETURN VARCHAR2;
    END tox;

How would I implement a session based global variable with PLpg/SQL?

Answer 1

From the Postgresql forums...

So, a couple of questions....

1. Can you declare global values from plpgsql?
2. If so, is there a way of avoiding namespace pollution? (perhaps the equivalent to Oracle's use of plsql package variables)

plpgsql does not have global variables.

Answer 2

PostgreSQL doesn't support global (session) variables, but you should use some tricks

http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables http://www.postgresql.org/docs/8.3/static/plperl-global.html

regards Pavel Stehule

Answer 3

You could define some custom-variable-classes in your postgresql.conf and use it as connection-variables in your stored-procedure. See the docs.

Usage example for a custom-variable-class "imos":

imos=> set imos.testvar to 'foobar';
SET
Time: 0.379 ms
imos=> show imos.testvar;
 imos.testvar
--------------
 foobar
(1 row)

Time: 0.333 ms
imos=> set imos.testvar to 'bazbar';
SET
Time: 0.144 ms
imos=> show imos.testvar;
 imos.testvar
--------------
 bazbar
(1 row)

In stored-procedures you can use the built-in function current_setting('imos.testvar').

Answer 4

Unfortunately there are no global variables in PL/pgSQL, although you can find ones in other PL languages that come with PostgreSQL, specifically in PL/Perl, PL/Python and PL/Tcl

Answer 5

Another option would be to create a temporary table, and use it to store all of your temporary variables

CREATE TEMPORARY TABLE tmp_vars( 
    name varchar(64),
    value varchar(64),
    PRIMARY KEY (name)
);

You could even create a stored procedure to manage everything, creating the table if it doesn't yet exist. One for retrieval and one for storage.

Answer 6

I am unable to make the PL/Perl example from the documentation work. The functions compile fine, but when using get_var my return message looks like:

prod1=# select set_var('foo','bar');

set_var

ok (1 row)

prod1=# select get_var('foo');

get_var

CODE(0x2033bd4) (1 row)

The example syntax given in the documentation is buggy (odd number of quotes), so i'm not encouraged that the code has been QA'd properly. Has anyone else made the code from the documentation work correctly?