On Tuesday 02 September 2003 16:40, you wrote:
> No problem to use a temp table in a trigger (which is local to the
> session), I do so myself. Still, session variables would be nice, making
> coding a little bit more comfortable.
(it would be very good if you implement session variables in PostgreSQL.)
The first problem using temp table is:
CREATE TABLE locals (name text, value text);
CREATE OR REPLACE FUNCTION test_locals_access() RETURNS text AS '
DECLARE i text;
BEGIN
SELECT value INTO i FROM locals WHERE name=''n1''
RETURN i;
END;
' LANGUAGE 'plpgsql';
SELECT test_locals_access() ;
column
-------------
<null>
CREATE TEMP TABLE locals (name text, value text);
INSERT INTO locals VALUES ('n1','xxx');
SELECT test_locals_access() ;
column
-------------
<null> <=== the Function seing global table
SELECT value FROM locals WHERE name='n1';
value
---------
xxx
the second problem may be resolved with your advise.
look:
i want to log operations on the data in some tables.
so i declared sufficient triggers which write to the log-table.
and now i want to mark each log-record with the "operator_id"
(e.g. to log who made an update)
what possible ways are there ?
if i use temp table to inform the triggers about "operator_id"
then i have problems in manual updates of these tables
(temp table in my session needed too)