How do I dynamically create a trigger function in pgsql?
I want to write a pgsql function to create a trigger dynamically. For example, a trigger to count the insertion 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. There was a major syntax error while importing this code. It seems that EXECUTE cannot complete function creation.
What else can you do to create trigger functions dynamically?
+3
source to share
1 answer
The two $$ sections were confused. Using the $ name $ syntax, you can separate them.
There is no RETURN in the trigger.
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;
+2
source to share