Set a temporary global variable during a Postgres query

Question

Is it possible to set a variable during a query (valid only for the query in question) that can be captured by a TRIGGER procedure?

For example, I want to record the ID of the executor of a query (current_user is always the same). So I would do something like this:

tbl_executor (
  id   PRIMARY KEY,
  name VARCHAR
);
tbl_log (
  executor REFERENCE tbl_executor(id),
  op VARCHAR
);
tbl_other ...

CREATE TRIGGER t AFTER INSERT OR UPDATE OR DELETE ON tbl_executor 
FOR EACH ROW 
EXECUTE PROCEDURE (INSERT INTO tbl_log VALUES( ID_VAR_OF_THIS_QUERY ,TG_OP))

Now if I run a query like:

INSERT INTO tbl_other 
VALUES(.......) - and set ID_VAR_OF_THIS_QUERY='id of executor' -

I get the following result:

           tbl_log
-----------------------------
id                | op      |
-----------------------------
'id of executor'  | 'INSERT'|

I hope I have made the idea... and I think it is hardly feasible... but is there anyone who could help me?

You can create a temporary table with `create if not exists`/ `on commit drop`, then put the data in there. — Ben, Apr 14 '19 at 13:32
Alternatively you can have a shared table where the PK is the current `pg_backend_pid()` and store the data in that. — Ben, Apr 14 '19 at 13:39

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

To answer the question

You can SET a (customized option) like this:

SET myvar.role_id = '123';

But that requires a literal value. There is also the function set_config(). Quoting the manual:

set_config(setting_name, new_value, is_local) ... set parameter and return new value

set_config sets the parameter setting_name to new_value. If is_local is true, the new value will only apply to the current transaction.

Correspondingly, read option values with SHOW or current_setting(). Related:

How to use variable settings in trigger functions?

But your trigger is on the wrong table (tbl_executor) with wrong syntax. Looks like Oracle code, where you can provide code to CREATE TRIGGER directly. In Postgres you need a trigger function first:

How to use PostgreSQL triggers?

So:

CREATE OR REPLACE FUNCTION trg_log_who()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO tbl_log(executor, op)
   VALUES(current_setting('myvar.role_id')::int, TG_OP);  -- !

   RETURN NULL;  -- irrelevant for AFTER trigger     
END
$func$  LANGUAGE plpgsql;

Your example setup requires the a type cast ::int.
Then:

CREATE TRIGGER trg_log_who
AFTER INSERT OR UPDATE OR DELETE ON tbl_other  -- !
FOR EACH ROW EXECUTE PROCEDURE trg_log_who();  -- !

Finally, fetching id from the table tbl_executor to set the variable:

BEGIN;
SELECT set_config('myvar.role_id', id::text, true)   -- !
FROM   tbl_executor
WHERE  name = current_user;

INSERT INTO tbl_other VALUES( ... );
INSERT INTO tbl_other VALUES( ... );
--  more?
COMMIT;

Set the the third parameter (is_local) of set_config() to true to make it session-local as requested. (The equivalent of SET LOCAL.)

But why per row? Would seem more reasonable to make it per statement?

...
FOR EACH STATEMENT EXECUTE PROCEDURE trg_foo();

Different approach

All that aside, I'd consider a different approach: a simple function returning the id a column default:

CREATE OR REPLACE FUNCTION f_current_role_id()
  RETURNS int LANGUAGE sql STABLE AS
'SELECT id FROM tbl_executor WHERE name = current_user';

CREATE TABLE tbl_log (
  executor int DEFAULT f_current_role_id() REFERENCES tbl_executor(id)
, op VARCHAR
);

Then, in the trigger function, ignore the executor column; will be filled automatically:

...
   INSERT INTO tbl_log(op) VALUES(TG_OP);
...

Be aware of the difference between current_user and session_user. See:

How to check role of current PostgreSQL user from Qt application?

@Phocs: You already accepted. But consider the improved answer. — Erwin Brandstetter, Apr 14 '19 at 23:24

Ben · Answer 2 · 2019-04-14T14:06:38.360

One option is to create a shared table to hold this information. Since it's per-connection, the primary key should be pg_backend_pid().


    create table connection_global_vars(
       backend_pid bigint primary key,
       id_of_executor varchar(50)
    );
    insert into connection_global_vars(backend_pid) select pg_backend_pid() on conflict do nothing;
    update connection_global_vars set id_of_executor ='id goes here' where backend_pid = pg_backend_pid();


    -- in the trigger: 

CREATE TRIGGER t AFTER INSERT OR UPDATE OR DELETE ON tbl_executor 
FOR EACH ROW 
EXECUTE PROCEDURE (INSERT INTO tbl_log VALUES( (select id_of_executor from connection_global_vars where backend_pid = pg_backend_pid()) ,TG_OP))

Another option is to create a temporary table (which exists per-connection).

 create temporary table if not exists connection_global_vars(
       id_of_executor varchar(50)
    ) on commit delete rows;
    insert into connection_global_vars(id_of_executor) select null where not exists (select 1 from connection_global_vars);
    update connection_global_vars set id_of_executor ='id goes here';



    -- in the trigger: 

CREATE TRIGGER t AFTER INSERT OR UPDATE OR DELETE ON tbl_executor 
FOR EACH ROW 
EXECUTE PROCEDURE (INSERT INTO tbl_log VALUES( (select id_of_executor from connection_global_vars where backend_pid = pg_backend_pid()) ,TG_OP))

For PostgreSQL in particular it probably won't make much difference to performance, except an unlogged temporary table may just possibly be slightly faster.

If you have performance issues around not recognising that it's a single row-table, you might run analyse.

Set a temporary global variable during a Postgres query

2 Answers2

To answer the question

Different approach