代码之家 › 专栏 › 技术社区 › ChuckE

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

postgresql-triggers plpgsql global-variables postgresql

ChuckE · 技术社区 · 7 年前

我想记录会话/事务中用户的id,使用 SET ,所以我可以稍后在触发器函数中访问它,使用 current_setting . 基本上,我在尝试 very similar ticket posted previously ,不同的是我用的是10.1页。

我尝试了三种设置变量的方法:

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

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

我得出的结论是,函数是在不同的事务和不同的(?)会议。这是一个可以配置的东西吗,以便所有的事情都发生在同一个上下文中?

3 回复 | 直到 7 年前

Erwin Brandstetter 7 年前

处理所有可能的案件 customized option 适当地:

所有对它的提及例外 ,包括 current_setting() missing_ok . The manual:

如果没有名为 setting_name current_setting 抛出错误,除非 不见了好的 true .
选项设置为有效的整数文本
选项重置(烧成特殊情况三。 )

例如,如果将自定义选项设置为 SET LOCAL 或 set_config('myvars.user_id3', '55', true) 存在 ,可以引用,但现在返回空字符串( '' integer .

抛开演示中明显的错误不谈,您需要为所有4个案例做好准备。所以:

CREATE OR REPLACE FUNCTION add_transition1()
  RETURNS trigger 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$  LANGUAGE plpgsql;

here

笔记:

_user_id .
在声明时分配以保存一个分配。注意数据类型 text
避免引发/捕获异常 . The manual:

EXCEPTION 条款要贵得多没有一个块进入和退出。因此,不要使用 例外情况
测试有效的整数字符串。这个简单的正则表达式只允许数字(没有前导符号,没有空格): _user_id ~ '^\d+$' . 对于任何无效输入,我将重置为空。适应你的需要。
我添加了一个可选的 WARNING 为了调试方便。
3. 和 4. 文本 ),无法自动强制执行有效的数据类型。

除此之外,根据您的具体需求,可能会有更优雅的解决方案来解决您在没有定制选项的情况下所要做的事情。也许这个:

Fastest way to get current user's OID in Postgres?

klin 7 年前

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

总是给予 无效的 .

garysieling 7 年前

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;