postgresql - 在不递归的情况下更新触发器中的行

Question

我有两张桌子：

CREATE TABLE first (
    id text primary key,
    updated_at timestamp,
    data text
);

CREATE TABLE second (
    id text REFERENCES first (id),
    book_error text,
);

当这些表中的任何一个更新时，我需要updated_at始终更新第一个表中的字段。我写了这个：

CREATE FUNCTION update_timestamp() RETURNS trigger AS $$
    BEGIN
        UPDATE first 
            SET updated_at = current_timestamp
            WHERE id = NEW.id;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE
    t text;
BEGIN
    FOR t IN 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = 'public'
    LOOP
        EXECUTE format('CREATE TRIGGER update_timestamp
                        BEFORE INSERT OR UPDATE ON %I
                        FOR EACH ROW EXECUTE PROCEDURE update_timestamp()',
                        t);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

但它不起作用，因为我的触发器中的更新语句导致在执行之前再次调用此触发器。
如何在不再次触发触发器的情况下更新内部触发器？

标签： postgresqltriggers

Answer 1

从Postgresql 文档中，

pg_trigger_depth()- PostgreSQL 触发器的当前嵌套级别（如果未从触发器内部直接或间接调用，则为 0）

您可以在触发器函数中使用它来检查它是否从触发器内部调用

DO $$
DECLARE
    t text;
BEGIN
    FOR t IN 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = 'public'
    LOOP
        EXECUTE format('CREATE TRIGGER update_timestamp
                        BEFORE INSERT OR UPDATE ON %I
                        FOR EACH ROW
                        WHEN (pg_trigger_depth() = 0)
                        EXECUTE PROCEDURE update_timestamp()',
                            t);
        END LOOP;
    END;
    $$ LANGUAGE plpgsql;


test=# Insert into first select 1,now(), 'test';
INSERT 0 1

test=# select * from first;
 id |         updated_at         | data 
----+----------------------------+------
 1  | 2018-10-29 20:18:25.227281 | test
(1 row)

test=# Insert into second select 1, 'test_error';
INSERT 0 1
test=# select * from first;
 id |         updated_at         | data 
----+----------------------------+------
 1  | 2018-10-29 20:19:07.456737 | test