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

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 2

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