在Postgres查询期间设置临时全局变量

Question

在查询过程中设置一个变量（仅对该查询有效），并能够被TRIGGER过程捕获，这种情况是否可能？

例如，我想记录查询执行者的ID（current_user始终相同）。 因此，我会像这样做：

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

现在，如果我运行这样的查询：

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

我得到了以下结果：

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

我希望我已经表达了这个想法......我认为这几乎是不可行的......但有没有人能帮助我？

- Phocs

---

你可以使用 create if not exists/on commit drop 创建一个临时表，然后将数据放入其中。 - Ben

或者您可以拥有一个共享表，其中PK是当前的 pg_backend_pid()，并将数据存储在其中。 - Ben

Answer 1

回答问题

您可以像这样设置一个（自定义选项）：

SET myvar.role_id = '123';

但这需要一个字面值。还有函数set_config()。引用手册:

set_config(setting_name, new_value, is_local)...设置参数并返回新值

set_config将参数setting_name设置为new_value。如果is_local为true，则新值仅适用于当前事务。

相应地，使用SHOW或current_setting()读取选项值。相关：

• 如何在触发器函数中使用变量设置？

但是您的触发器位于错误的表(tbl_executor)上，语法也有误。看起来像Oracle代码，可以直接提供代码给CREATE TRIGGER。在Postgres中，您需要首先创建一个触发器函数:

• 如何使用PostgreSQL触发器？

所以：

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;

您的示例设置需要进行类型转换::int。
然后：

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

最后，从表格 tbl_executor 中获取 id 并将其设置为变量：

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_config()的第三个参数(is_local)设置为true，以满足要求使其成为会话本地化。(相当于SET LOCAL。)

但是为什么是按行而不是按语句呢？看起来按语句更合理？

...
FOR EACH <b>STATEMENT</b> EXECUTE PROCEDURE trg_foo();

不同的方法

抛开以上内容，我认为可以采用不同的方法：一个简单的函数返回列默认值的id：

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

然后，在触发器函数中，忽略executor列；它将自动填充：

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

请注意current_user和session_user之间的区别。参见：

• 如何从Qt应用程序中检查当前PostgreSQL用户的角色？

- Erwin Brandstetter

---

@Phocs：你已经接受了。但请考虑改进的答案。 - Erwin Brandstetter

Answer 2

一个选择是创建一个共享表来保存这些信息。由于它是每个连接的，因此主键应该是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))

另一个选项是创建一个临时表（这个表存在于每个连接中）。

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

特别是对于PostgreSQL，这可能不会对性能产生很大影响，除非未记录的临时表可能会稍微快一些。

如果您在识别单行表方面遇到性能问题，可以运行分析。

- Ben