Session based global variable in Postgresql stored procedure?

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').

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.

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

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

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

regards Pavel Stehule

An example PL/pgsql script that stores and retrieves global variables from a table:

CREATE TABLE global_vars (name TEXT PRIMARY KEY, value TEXT);

CREATE FUNCTION put_var(key TEXT, data TEXT) RETURNS VOID AS '
  BEGIN
    LOOP
        UPDATE global_vars SET value = data WHERE name = key;
        IF found THEN
            RETURN;
        END IF;
        BEGIN
            INSERT INTO global_vars(name,value) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
  END;
' LANGUAGE plpgsql;

CREATE FUNCTION get_var(key TEXT) RETURNS TEXT AS '
  DECLARE
    result TEXT;
  BEGIN
    SELECT value FROM global_vars where name = key INTO result;
    RETURN result;
  END;
' LANGUAGE plpgsql;


CREATE FUNCTION del_var(key TEXT) RETURNS VOID AS '
  BEGIN
    DELETE FROM global_vars WHERE name = key;
  END;
' LANGUAGE plpgsql;

Nowadays, PostgreSQL does allow you to define custom session or transaction-local settings to your heart's content.

SET myprefix.my_bool_setting TO true;
SET myprefix.my_text_setting TO 'something';

Just remember to cast these settings to the correct type when you retrieve them, because they are all stored as strings:

SELECT current_setting('myprefix.my_bool_setting', true)::bool;
SELECT current_setting('myprefix.my_text_setting', true);

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.