Postgresql – How to write an “on-update” trigger which takes a table-name as argument

postgresqltrigger

NOTE: The following example is a strongly simplified code, but it illustrates the task in a reproducible example. Looking at this example may make you think "why do it like this"? In reality, the full task is to store an audit-trail for multiple tables, but only if certain conditions are met. The condition is the same for each table (they each share some columns like inserted, updated and so on). So the code when to store an audit-trail is the same for each table. But the actual columns to be copied are different each time. I weant to create a trigger which handles this dynamically such that I don't need to touch it each time the schema changes.

Consider the following working example (question below). This demonstrates a simple schema where every update in table data causes the old values to be moved into data2:

DROP TABLE IF EXISTS data CASCADE;
DROP TABLE IF EXISTS data2 CASCADE;
CREATE TABLE data (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);
CREATE TABLE data2 (
    id SERIAL,
    name TEXT,
    updated TIMESTAMP WITH TIME ZONE
);

CREATE OR REPLACE FUNCTION update_trigger_func()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.updated = NOW();
        INSERT INTO data2 VALUES (OLD.*);
        RETURN NEW;
    END;
    $$ language 'plpgsql';

CREATE TRIGGER update_trigger
    BEFORE UPDATE ON data
    FOR EACH ROW
    EXECUTE PROCEDURE update_trigger_func();


SET client_min_messages TO 'debug';
INSERT INTO data (name) VALUES ('foo');
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'bar';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

SELECT pg_sleep(1);
UPDATE data SET name = 'baz';
COMMIT;  -- Make sure we get new timestamps from NOW()
SELECT * FROM ONLY data;
SELECT * FROM ONLY data2;

Notice that the function update_trigger_func has the "history" table name hard-coded as data2 on the line which reads:

INSERT INTO data2 VALUES (OLD.*);

If data2 was an argument, this function could be reusable for other tables as well. But so far I failed to find the right incantation. I've tried the following two versions so far:

INSERT INTO TG_ARGV[0] VALUES (OLD.*);

But this causes a syntax error:

psql:temptable.sql:28: ERROR:  syntax error at or near "VALUES"
LINE 11:         INSERT INTO TG_ARGV[0] VALUES (OLD.*);

So alternatively I tried with dynamic SQL:

sql := 'INSERT INTO' || TG_ARGV[0] || 'VALUES (OLD.*)';
EXECUTE sql;

But that fails because the OLD variable is not available in the execution context:

psql:temptable.sql:58: ERROR:  missing FROM-clause entry for table "old"
LINE 1: INSERT INTO data2 VALUES (OLD.*)
                                    ^
QUERY:  INSERT INTO data2 VALUES (OLD.*)
CONTEXT:  PL/pgSQL function versioned_update() line 11 at EXECUTE

Given that I would like to use this trigger-function on other tables I can't hard-code the column names. How could I achieve this?

Best Answer

Unfortunately, googling this was fairly difficult as the data-type of OLD is data, and searching for this in Google turned up nothing usable. Additionally, finding out that data is a composite type was key to finding the solution.

The issue is twofold:

  • The table cannot directly be used in an INSERT statement as it is of type text. So dynamic SQL has to be used. So instead of directly writing

    INSERT INTO TG_ARGV[0] VALUES OLD;

    the following had to be used:

    sql := format(INSERT INTO %I VALUES ...', TG_ARGV[0]);

  • The OLD variable is not available directly inside an EXECUTE statement. So, building from the previous bullet-point, this was not possible:

     sql := format(INSERT INTO %I VALUES OLD', TG_ARGV[0]);

    instead the values from OLD had to be passed on with the USING keyword:

     sql := format('INSERT INTO %I VALUES $1.*', TG_ARGV[0]);
 EXECUTE sql USING OLD;

The final solution:

CREATE OR REPLACE FUNCTION update_trigger_func()
    RETURNS TRIGGER AS $$
    DECLARE
        sql TEXT;
    BEGIN
        NEW.updated = NOW();
        sql := format('INSERT INTO %I VALUES $1.*', TG_ARGV[0]);
        EXECUTE sql USING OLD;
        RETURN NEW;
    END;
    $$ language 'plpgsql';

This allows us to write a trigger which handles events on the data table and writes the history into history table as:

 CREATE TRIGGER data_versioning_trigger_delete
     BEFORE DELETE ON data
     FOR EACH ROW EXECUTE PROCEDURE update_trigger_func('history');
Related Question