Postgres dynamic insert in trigger function

Question

I am trying to create a trigger function that insert a new record to the audit tables dynamically.

CREATE OR REPLACE FUNCTION schema.table()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    _tablename text;
    BEGIN
        _tablename := 'user_audit';
        EXECUTE 'INSERT INTO audit.' || quote_ident(_tablename) || ' VALUES ($1.*)' USING OLD;
        RETURN NEW;
    END;
$BODY$;

Trigger function above works fine as it take everything in OLD and inserts it into the audit table as expected. However I have a tstzrange range column in my tables called timestampzt_range and what I need to do is set the value for it in audit table using LOWER(OLD.timestampzt_range), LOWER(NEW.timestampzt_range). How can I achieve this dynamically without using insert statement like below as I would like to use this trigger function on multiple tables.

INSERT INTO audit.user_audit
    (
        column_1,
        column_2,
        timestampzt_range 
    )
    VALUES
    (
        OLD.column_1,
        OLD.column_2,       
        tstzrange(LOWER(OLD.timestampzt_range), LOWER(NEW.timestampzt_range))
    );

I only need to use this on update and table name will be passed as a parameter to the trigger function if I can achieve dynamic statement. Only the audit columns are consistent across the entire database so it is important for me create insert using OLD or somehow dynamically extract everything from it but the timestampzt_range and then use tstzrange(LOWER(OLD.timestampzt_range), LOWER(NEW.timestampzt_range)) as value for the range column.

Edouard · Accepted Answer · 2021-11-08 17:31:41Z

0

First comment : you can use the NEW and OLD key words only in an UPDATE trigger. In an INSERT trigger, OLD doesn't exist. In a DELETE trigger, NEW doesn't exist. See the manual.

Then you can replace in your trigger function

'INSERT INTO audit.' || quote_ident(_tablename) || ' VALUES ($1.*)' USING OLD

by

'INSERT INTO audit.' || quote_ident(_tablename) || ' VALUES (OLD.column_1, OLD.column_2, tstzrange(LOWER(OLD.timestampzt_range), LOWER(NEW.timestampzt_range)))'

so that to achieve your expected result.

Last but not least, your dynamic statement EXECUTE 'INSERT INTO audit.' ... is useless because _tablename is a static parameter declared inside the function.

edited Nov 8, 2021 at 17:31

answered Nov 8, 2021 at 17:26

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4676307 Over a year ago

@ Edouard H. I only need to use this on update and table name will be passed as a parameter to the trigger function if I can achieve dynamic statement. Only the audit columns are consistent across the entire database so it is important for me create insert using OLD or somehow dynamically extract everything from it but the timestampzt_range and then use tstzrange(LOWER(OLD.timestampzt_range), LOWER(NEW.timestampzt_range)) as value for the range column.

Edouard Over a year ago

The trigger functions don't accept input parameters, see the manual : "A trigger function is created with the CREATE FUNCTION command, declaring it as a function with no arguments". Then OLD.timestampzt_range is not accepted in an INSERT trigger, because it just makes no sense. The question is : where does this information come from when you insert a new row ? And generally speaking, you should come back to your need instead of digging a solution which may not fit your need.

Collectives™ on Stack Overflow

Postgres dynamic insert in trigger function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related