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;
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
