2

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?

  • Do you obtain the same error message if you use only one field, like sql := 'INSERT INTO' || TG_ARGV[0] || '(somefieldname) VALUES (OLD.somefieldname)'; ? – Akina Sep 17 '18 at 12:31
  • If so - try to build full query text by inserting valies from OLD table as literals. Like sql := 'INSERT INTO' || TG_ARGV[0] || ' (field1, field2, ...) VALUES (\'' || OLD.field1 || '\',\'' || OLD.field2 || .... '\')'; And do not forget to quote special symbols (by proper REPLACEs) in literals... But the better way avoiding those replaces is to use prepared statement with values from OLD as its parameters. – Akina Sep 17 '18 at 12:34
  • Well, explicitly naming all columns is something I need to avoid. By naming all columns manually, the trigger function will be tightly coupled to that one table and cannot be reused for others. Additionally, if the table structure changes, the trigger needs to be changed as well. – exhuma Sep 17 '18 at 12:50
  • Take a look at the (different) examples in this answer to a similar question; of interest is the ability to pass a table (name) to the function; what I don't know is if either of these examples is passing an in-memory reference as opposed to just passing a name. – markp Sep 17 '18 at 12:53
  • @markp I already came across something like that, which prompted the last example. The problem with the dynamic SQL is that the variable OLD is not defined in that execution context. – exhuma Sep 17 '18 at 12:58
0

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');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.