CODEWITHSUNDEEP
postgresqlplpgsqldatabase-trigger
A. L
A. L

Reputation: 12689

postgres - creating trigger functions with arguments

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

Upvotes: 3

Views: 5395

Answers (2)

A. L
A. L

Reputation: 12689

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

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 248215

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.

Upvotes: 2

Related Questions