Postgresql – Is it possible to pass a built in function as an argument to a trigger

postgresqltrigger

I ask this question in light of the following trigger producing this error:

ERROR: syntax error at or near "("
LINE 5: ...cessBlogPostApproval"('Blog Post Approval', concat('Your blo...
^

The trigger in question:

CREATE TRIGGER "processBlogPostApproval_AFTER_INSERT"
    AFTER INSERT
    ON public."ApprovedBlogPosts"
    FOR EACH ROW
    EXECUTE PROCEDURE public."processBlogPostApproval"('Blog Post Approval', concat('Your blog post, "', SELECT "Title" FROM public."BlogPosts" WHERE "PostID" == NEW."PostID", '"has been approved.'));

The problem seems to be arising due to the fact that I passed a concatenation function as my second argument, or rather, that I did not pass it correctly. Would appreciate your assistance in identifying which of the two is the cause of the problem.

Best Answer

The CREATE TRIGGER documentation says:

arguments: An optional comma-separated list of arguments to be provided to the function when the trigger is executed. The arguments are literal string constants. Simple names and numeric constants can be written here, too, but they will all be converted to strings.

You cannot do any processing before the function is called; you have to put all the logic into the trigger function.

Related Solutions

Postgresql – Passing argument in trigger dynamically

If you want to pass data to a trigger that is not provided by one of the predefined variables you could put that data in a temp table before you perform the INSERT/UPDATE/DELETE that will fire the trigger. Then you can retrieve the data from the table inside the trigger function. Temp tables are local to sessions so this is safe to do when multiple clients connect to the same database.

PostgreSQL – Pass Additional Data in Connection for PGSQL Trigger

Old question, but still valid, so here is what I ended up using.

On 9.6 and above, to set the values either of the following will work:

select set_config('app.userid', '3', true)
SET LOCAL "app.userid" = 3; (or '3')

To read the value out,

select current_value('app.userid')

I ended up wrapping the lookup for the userid or other field in a function, to handle the type conversion(s) and the fact that it might not be set:

CREATE OR REPLACE FUNCTION app_get_userid() RETURNS INT AS $$
DECLARE userid integer;
BEGIN
    BEGIN
        userid := current_setting('app.userid');
    EXCEPTION
        WHEN OTHERS THEN userid := NULL;
    END;
    return userid;
END;
$$ LANGUAGE plpgsql;

Best Answer

Related Solutions

Postgresql – Passing argument in trigger dynamically

PostgreSQL – Pass Additional Data in Connection for PGSQL Trigger

Related Question