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;

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

Answer 1

根据 the documentation:

TG_TABLE_NAME

Data type name; the name of the table that caused the trigger invocation.

在触发函数中使用变量:

CREATE OR REPLACE FUNCTION update_timestamp() RETURNS trigger AS $$
BEGIN
    IF TG_TABLE_NAME = 'first' THEN
        NEW.updated_at = current_timestamp;
    ELSE
        UPDATE first 
            SET updated_at = current_timestamp
            WHERE id = NEW.id;          
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;