Alex Rivera | Logout

Session based global variable in Postgresql stored procedure?

Asked 2009-01-05T21:09:59.457
9

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?

oracle-database postgresql plsql plpgsql
Edit
Report

2 Answers

3

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

answered 2009-09-05T03:56:36.703
2

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;
answered 2012-01-11T08:19:20.793

Your Answer