Temp tables as session var containers

Started by James Croftalmost 21 years ago2 messagesgeneral
Jump to latest
#1James Croft
james.croft@lumison.net

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

#2Richard Huxton
dev@archonet.com
In reply to: James Croft (#1)
Re: Temp tables as session var containers

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

Attachments:

session_functions.txttext/plain; name=session_functions.txtDownload