How to create a trigger function dynamically in pgsql?

Question

I want to write a pgsql function to create trigger dynamically. For example, a trigger to count insertions in each table. I've tried EXECUTE like this:

CREATE FUNCTION trigen(tbl text) RETURNS void AS $$
BEGIN
    EXECUTE format(
    'CREATE FUNCTION %s_insertCnt() RETURNS TRIGGER AS $$
    BEGIN
        UPDATE insertions SET n = n + 1 WHERE tablename = %s;
    END
    $$ LANGUAGE plpgsql', tbl, quote_nullable(tbl));

    EXECUTE format('CREATE TRIGGER %s_inCnt BEFORE INSERT ON %s
    FOR EACH ROW EXECUTE PROCEDURE %s_insertCnt();', tbl, tbl, tbl);
END
$$ LANGUAGE plpgsql

But this approach doesn't work. A lot of syntax error occurred when I import this code. It seems that EXECUTE cannot execute a function creation.

What else can I do to create trigger functions dynamically?

I think the problem is the nested $$. To nest dollar-quoted literals, you need to pick different tags. See the docs for details. — Nick Barnes
– Nick Barnes, Commented Jul 20, 2015 at 3:34

Gary - Stand with Ukraine · Accepted Answer · 2015-07-20 07:44:17Z

5

The two $$ sections were getting confused. By using the $name$ syntax instead you can separate these.

Also the trigger was missing a RETURN.

CREATE OR REPLACE FUNCTION trigen(tbl text) RETURNS void AS $T1$
BEGIN
    EXECUTE format(
    'CREATE FUNCTION %s_insertCnt() RETURNS TRIGGER AS $T2$
    BEGIN
        UPDATE insertions SET n = n + 1 WHERE tablename = %s;
        RETURN NEW;
    END
    $T2$ LANGUAGE plpgsql', tbl, quote_nullable(tbl));

    EXECUTE format('CREATE TRIGGER %s_inCnt BEFORE INSERT ON %s
    FOR EACH ROW EXECUTE PROCEDURE %s_insertCnt();', tbl, tbl, tbl);
    END
$T1$ LANGUAGE plpgsql;

answered Jul 20, 2015 at 7:44

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

How to create a trigger function dynamically in pgsql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related