如何在pgsql中动态创建触发函数? [英] How to create a trigger function dynamically in pgsql?
本文介绍了如何在pgsql中动态创建触发函数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想编写一个pgsql函数来动态创建触发器.例如,一个用于对每个表中的插入进行计数的触发器.我已经试过像这样执行:
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
但是这种方法行不通.导入此代码时发生很多语法错误.看来EXECUTE无法执行函数创建.
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?
推荐答案
两个$$部分变得混乱.通过使用$ name $语法,您可以将它们分开.
The two $$ sections were getting confused. By using the $name$ syntax instead you can separate these.
触发器也没有返回.
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;
这篇关于如何在pgsql中动态创建触发函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文