Re: [BUGS] session variable

sad writes:

> 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

This is a problem in plpgsql, not in temporary tables.

> 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)

Use session_user, or restrict permissions on the temporary table.

-- 
Peter Eisentraut   peter_e@gmx.net