我想编写一个pgsql函数来动态创建触发器.例如,用于计算每个表中插入的触发器.我试过这样的EXECUTE：
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);
END
$$LANGUAGE plpgsql
但这种方法不起作用.导入此代码时发生了很多语法错误.似乎EXECUTE无法执行函数创建.
我还能做些什么来动态创建触发器功能？
两个$$部分变得混乱.通过使用$name $语法,您可以将它们分开.
触发器也缺少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);
END
$T1$LANGUAGE plpgsql;
