Thread: Temp tables as session var containers

Hi,

I've seen the session variable question pop up a fair bit on this list.
The temporary table solution seems good but I've got a question before
using it...

- My app creates a temp table for session vars
- UPDATE, INSERT and DELETE triggers on tables use this data

My question is:

If I run a query directly through the psql command line tool (or another
app that doesn't setup this temp table) that temp table wont exist.

How can I write the trigger function to detect the absence of the temp
table and deal with it gracefully? I think I need some SQL to determine
which pg_temp_N schema belongs to my session.

Is this possible?

TIA,
James

James Croft wrote:
> Hi,
>
> I've seen the session variable question pop up a fair bit on this list.
> The temporary table solution seems good but I've got a question before
> using it...

Another option is to use one of the procedural languages that provide
global variable storage. The attached examples are in TCL.

Set the user-id
   SELECT app_session('UID', 'ABC1234');
Get the user-id
   SELECT app_session('UID');

--
   Richard Huxton
   Archonet Ltd
-- app_session(VARNAME, VALUE)
--    Defines a text variable and sets its value.
--    If you try to set the same VARNAME twice in one session, an error is returned.
--    If VALUE is null, just returns the value.
--
CREATE OR REPLACE FUNCTION app_session(text, text) RETURNS text AS '
    upvar app_sess_vars a
    if {![ argisnull 2 ]} {
        if {[ info exists a($1) ]} {
            elog ERROR "app_session(): Already set var $1 this session"
        }
        set a($1) $2
    }

    return $a($1)
' LANGUAGE pltcl;


-- app_session(VARNAME)
--    Returns the value of VARNAME (if set) or "UNDEFINED"
--    NOTE - this function is marked IMMUTABLE - do not use as a DEFAULT source
--  in a table definition
--
CREATE OR REPLACE FUNCTION app_session(text) RETURNS text AS '
    upvar app_sess_vars a
    if {![ info exists a($1) ]} {
        return "UNDEFINED"
    }

    return $a($1)
' LANGUAGE pltcl IMMUTABLE;

-- app_session_int(VARNAME)
--    Returns the value of VARNAME (if set) or 0
--    NOTE - this function is marked IMMUTABLE - do not use as a DEFAULT source
--  in a table definition
--
CREATE OR REPLACE FUNCTION app_session_int(text) RETURNS int4 AS '
    upvar app_sess_vars a
    if {![ info exists a($1) ]} {
        return 0
    }

    return $a($1)
' LANGUAGE pltcl IMMUTABLE;


-- app_session_vol(VARNAME)
--    Returns the value of VARNAME (if set) or "UNDEFINED"
--    NOTE - this function is marked IMMUTABLE - do not use as a DEFAULT source
--  in a table definition
--
CREATE OR REPLACE FUNCTION app_session_vol(text) RETURNS text AS '
    upvar app_sess_vars a
    if {![ info exists a($1) ]} {
        return "UNDEFINED"
    }

    return $a($1)
' LANGUAGE pltcl VOLATILE;


-- app_session_int_vol(VARNAME)
--    Returns the value of VARNAME (if set) or 0
--    NOTE - this function is marked VOLATILE
--
CREATE OR REPLACE FUNCTION app_session_int_vol(text) RETURNS int4 AS '
    upvar app_sess_vars a
    if {![ info exists a($1) ]} {
        return 0
    }

    return $a($1)
' LANGUAGE pltcl VOLATILE;