postgres - creating trigger functions with arguments

Question

I was wondering if it was possible to create a trigger function with arguments, since the function's logic is the same, but may just insert into different tables and column names.

Here's the function:

CREATE OR REPLACE FUNCTION 
    createInstance(table_name TEXT, column_name TEXT) 
RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO
        table_name
        (
            column_name,
            account_id
        )
    VALUES
        (
            new._id,
            new.account_id
        );

    RETURN new;
END;
$BODY$
language plpgsql;

The error:

ERROR:  trigger functions cannot have declared arguments
HINT:  The arguments of the trigger can be accessed through TG_NARGS and TG_ARGV instead.
CONTEXT:  compilation of PL/pgSQL function "createinstance" near line 1

Example trigger:

CREATE TRIGGER trig_instanced_item_copy
    AFTER INSERT ON creator.items
    FOR EACH ROW
    EXECUTE PROCEDURE createInstance();

You can't use a variable as an identifier. You have to use dynamic SQL if you want the table name to be dynamic — , Feb 07 '19 at 08:01
@a_horse_with_no_name could you make an example with `TG_ARGV` if you know how to? — A. L, Feb 07 '19 at 08:07

score 2 · Answer 1 · answered Feb 07 '19 at 07:39

2

You don't define parameters for a trigger function, but you can still specify arguments to it in the CREATE TRIGGER statement.

These arguments have to be constants and will be available to the trigger function via the TG_ARGV array.

That way you can parameterize the trigger for each table, for example by passing the names of tables and columns the trigger should operate on. You'll have to use dynamic SQL using EXECUTE in the trigger in this case.

answered Feb 07 '19 at 07:39

Laurenz Albe

209,280
17
206
263

Could you make an example? I'll update my question with an example I found online. – A. L Feb 07 '19 at 07:42
No; it is straightforward, but quite a lot of code to write. – Laurenz Albe Feb 07 '19 at 08:30
@A.L see [this](https://stackoverflow.com/a/33668137/3429115) as a good example. +1 for the Dynamic SQL note. – Rafs Mar 03 '23 at 15:25

score 1 · Answer 2 · answered Feb 07 '19 at 16:43

Couldn't figure out how to do it dynamically so used this:

CREATE OR REPLACE FUNCTION 
    insertIntoInstancedItem () 
RETURNS TRIGGER AS $BODY$
DECLARE
BEGIN
    INSERT INTO
        creator.instanced_items
        (
            item_id,
            account_id
        )
    VALUES
        (
            new._id,
            new.account_id
        );

    RETURN 
        *;
END
$BODY$
language plpgsql;        

CREATE TRIGGER trig_item_insertion
    AFTER INSERT ON creator.items
    FOR EACH ROW
    EXECUTE PROCEDURE insertIntoInstancedItem();

postgres - creating trigger functions with arguments

2 Answers2