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

Question

我想使用SET在会话/事务中记录用户的id，以便稍后可以使用current_setting在触发器函数中访问它。基本上，我正在尝试第二种选项，与之前发布的非常相似的票证不同的是，我正在使用PG 10.1。

我一直在尝试三种方法来设置变量：

- SET local myvars.user_id = 4，从而在事务中本地设置它； - SET myvars.user_id = 4，从而在会话中设置它； - SELECT set_config('myvars.user_id', '4', false)，根据最后一个参数，将是前两个选项的快捷方式。

这些方法都不能在触发器中使用，当通过current_setting获取变量时，会收到null的返回值。下面是我设计的脚本，用于解决问题（可以轻松使用postgres docker镜像）：

database=$POSTGRES_DB
user=$POSTGRES_USER
[ -z "$user" ] && user="postgres"

psql -v ON_ERROR_STOP=1 --username "$user" $database <<-EOSQL
    DROP TRIGGER IF EXISTS add_transition1 ON houses;
    CREATE TABLE IF NOT EXISTS houses (
        id SERIAL NOT NULL,
        name VARCHAR(80),
        created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
        PRIMARY KEY(id)
    );

    CREATE TABLE IF NOT EXISTS transitions1 (
        id SERIAL NOT NULL,
        house_id INTEGER,
        user_id INTEGER,
        created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
        PRIMARY KEY(id),
        FOREIGN KEY(house_id) REFERENCES houses (id) ON DELETE CASCADE

    );

    CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS \$\$
        DECLARE
            user_id integer;
        BEGIN
            user_id := current_setting('myvars.user_id')::integer || NULL;
            INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id);
            RETURN NULL;
        END;
    \$\$ LANGUAGE plpgsql;

    CREATE TRIGGER add_transition1 AFTER INSERT OR UPDATE ON houses FOR EACH ROW EXECUTE PROCEDURE add_transition1();

    BEGIN;
    %1% SELECT current_setting('myvars.user_id');
    %2% SELECT set_config('myvars.user_id', '55', false);
    %3% SELECT current_setting('myvars.user_id');
    INSERT INTO houses (name) VALUES ('HOUSE PARTY') RETURNING houses.id;
    SELECT * from houses;
    SELECT * from transitions1;
    COMMIT;
    DROP TRIGGER IF EXISTS add_transition1 ON houses;
    DROP FUNCTION IF EXISTS add_transition1;
    DROP TABLE transitions1;
        DROP TABLE houses;
EOSQL

我得出的结论是该函数在不同的事务和不同的（？）会话中触发。这是否可以配置，使所有操作在相同的上下文中完成？

- ChuckE

Answer 1

Handle all possible cases for the 自定义选项 properly:

1. 选项尚未设置

所有对它的引用都会引发一个异常，包括current_setting()，除非第二个参数missing_ok被调用。手册：

如果没有名为setting_name的设置，则current_setting会抛出一个错误，除非提供并且为true的missing_ok。

2. 选项设置为有效的整数文字

3. 选项设置为无效的整数文字

4. 选项重置（这归结为3.的特殊情况）

例如，如果您使用 SET LOCAL 或 set_config('myvars.user_id3', '55', true) 设置自定义选项，则选项值在事务结束时将重置。它仍然存在，可以被引用，但现在返回一个空字符串（''） - 不能转换为整数。

除了您演示中的明显错误外，您需要为所有4种情况做好准备。所以：

CREATE OR REPLACE FUNCTION add_transition1()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _user_id text := current_setting('myvars.user_id', true);  -- see 1.
BEGIN
   IF _user_id ~ '^\d+$' THEN  -- one or more digits?
      INSERT INTO transitions1 (user_id, house_id)
      VALUES (_user_id::int, NEW.id);  -- valid int, cast is safe
   ELSE
      INSERT INTO transitions1 (user_id, house_id)
      VALUES (null, NEW.id);           -- use null instead

      RAISE WARNING 'Invalid user_id % for house_id % was reset to null!'
                  , quote_literal(_user_id), NEW.id;  -- optional
   END IF;

   RETURN NULL;  -- OK for AFTER trigger
END
$func$;

db<>fiddle here

注：

• 避免变量名与列名相同，这种情况容易出错。一种流行的命名约定是在变量名前加下划线：_user_id。

• 声明时赋值可以节省另一个赋值操作。注意数据类型text。我们将在解决无效输入后进行转换。

• 尽可能避免引发/捕获异常。手册:

  包含EXCEPTION子句的块进入和退出的成本比没有子句的块高得多。因此，不需要使用EXCEPTION。

• 测试有效的整数字符串。这个简单的正则表达式只允许数字（没有前导符号，没有空格）：_user_id ~ '^\d+$'。对于任何无效的输入，我将其重置为null。请根据您的需求进行调整。

• 我添加了一个可选的WARNING，方便您进行调试。

• 情况3和4仅出现因为自定义选项是字符串文字（类型text），不能自动强制执行有效的数据类型。

相关：

• PostgreSQL中的用户定义变量
• 在PostgreSQL查询中定义命名常量的方法是否存在？

除此之外，根据您的确切要求，可能有更优雅的解决方案，而无需定制选项。也许这个：

• 在Postgres中获取当前用户OID的最快方法？

- Erwin Brandstetter

---

你的回答是最全面的，尽管我选择了@garysieling回答的变体。但你涉及到了大部分重要问题；在开始时，myvars.user_id是NULL，在使用SET LOCAL后，它会在事务结束时重置为''，我必须考虑所有情况。 - ChuckE

Answer 2

目前不清楚您为何试图将NULL和user_id连接起来，但这显然是问题的根源。请删除它：

CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS $$
    DECLARE
        user_id integer;
    BEGIN
        user_id := current_setting('myvars.user_id')::integer;
        INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

请注意，这里的内容。

SELECT 55 || NULL

总是返回 NULL。

- klin

---

但是我该如何设置等效项呢？当设置不可用时，我想要得到NULL返回。 - ChuckE

@ChuckE user_id := current_setting('myvars.user_id', true)::integer; 用户ID := current_setting('myvars.user_id', true)::整数; - Abelisto

@ChuckE PS：我有一种感觉，你正在混淆 SQL 字符串连接运算符 || 和 C 逻辑“或”运算符 ||... - Abelisto

Answer 3

当值不存在时，您可以捕获异常-这是我所做的更改以使其正常工作：

CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS $$
    DECLARE
        user_id integer;
    BEGIN
        BEGIN
            user_id := current_setting('myvars.user_id')::integer;
        EXCEPTION WHEN OTHERS THEN
            user_id := 0;
        END;

        INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

 CREATE OR REPLACE FUNCTION insert_house() RETURNS void as $$
 DECLARE
    user_id integer;
 BEGIN 
   PERFORM set_config('myvars.user_id', '55', false);

   INSERT INTO houses (name) VALUES ('HOUSE PARTY');
 END; $$ LANGUAGE plpgsql;

- garysieling

---

尽管我没有选择它，但你的示例为我最终的实现提供了蓝图。非常感谢！ - ChuckE