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 — user330315
– user330315, Commented Feb 7, 2019 at 8:01
@a_horse_with_no_name could you make an example with TG_ARGV if you know how to? — A. L
– A. L, Commented Feb 7, 2019 at 8:07

Laurenz Albe · Accepted Answer · 2019-02-07 07:39:19Z

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 7, 2019 at 7:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

A. L Over a year ago

Could you make an example? I'll update my question with an example I found online.

Laurenz Albe Over a year ago

No; it is straightforward, but quite a lot of code to write.

Rafs Over a year ago

@A.L see this as a good example. +1 for the Dynamic SQL note.

A. L · Accepted Answer · 2019-02-07 16:43:41Z

1

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

answered Feb 7, 2019 at 16:43

A. L

12.7k29 gold badges100 silver badges179 bronze badges

Collectives™ on Stack Overflow

postgres - creating trigger functions with arguments

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related