在触发器函数中使用动态表名进行 INSERT
我不确定如何实现类似以下内容:
CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$
DECLARE
shadowname varchar := TG_TABLE_NAME || 'shadow';
BEGIN
INSERT INTO shadowname VALUES(OLD.*);
RETURN OLD;
END;
$$
LANGUAGE plpgsql;
即将值插入具有动态生成名称的表中。
执行上面的代码会产生:
ERROR: relation "shadowname" does not exist
LINE 1: INSERT INTO shadowname VALUES(OLD.*)
这似乎表明变量没有扩展/允许作为表名。我在 Postgres 手册中没有找到对此的引用。
我已经像这样尝试过 EXECUTE
:
EXECUTE 'INSERT INTO ' || quote_ident(shadowname) || ' VALUES ' || OLD.*;
但没有运气:
ERROR: syntax error at or near ","
LINE 1: INSERT INTO personenshadow VALUES (1,sven,,,)
RECORD
类型似乎丢失了:OLD.*
似乎已转换到一个字符串并重新解析 get,导致各种类型问题(例如 NULL
值)。
有什么想法吗?
I'm not sure how to achieve something like the following:
CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $
DECLARE
shadowname varchar := TG_TABLE_NAME || 'shadow';
BEGIN
INSERT INTO shadowname VALUES(OLD.*);
RETURN OLD;
END;
$
LANGUAGE plpgsql;
I.e. inserting values into a table with a dynamically generated name.
Executing the code above yields:
ERROR: relation "shadowname" does not exist
LINE 1: INSERT INTO shadowname VALUES(OLD.*)
It seems to suggest variables are not expanded/allowed as table names. I've found no reference to this in the Postgres manual.
I've already experimented with EXECUTE
like so:
EXECUTE 'INSERT INTO ' || quote_ident(shadowname) || ' VALUES ' || OLD.*;
But no luck:
ERROR: syntax error at or near ","
LINE 1: INSERT INTO personenshadow VALUES (1,sven,,,)
The RECORD
type seems to be lost: OLD.*
seems to be converted to a string and get's reparsed, leading to all sorts of type problems (e.g. NULL
values).
Any ideas?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
现代 PostgreSQL
format()
有一种内置的方法来转义标识符。比以前更简单:使用
VALUES
表达也是如此。db<>fiddle 此处
旧sqlfiddle
要点
format()
或quote_ident()
引用标识符(自动且仅在必要时),从而防御 SQL 注入和简单的语法违规。即使使用您自己的表名称,这也是必要的!
search_path
设置,裸表名称可能会解析为相同的另一个表不同模式中的名称。EXECUTE
。USING
子句安全地传递值。RETURN OLD;
对于触发器BEFORE DELETE
是必需的。 手册中的详细信息。您收到错误消息< /strong> 在您几乎成功的版本中,因为
OLD
在EXECUTE
中不可见。如果您想像您尝试的那样连接分解行的各个值,则必须使用quote_literal()
准备每个列的文本表示形式,以保证语法有效。您还必须事先知道列名称来处理它们或查询系统目录 - 这违背了您拥有简单的动态触发函数的想法......我的解决方案避免了所有这些复杂性。也简化了一点。
PostgreSQL 9.0 或更早
版本
format()
尚不可用,因此:相关:
Modern PostgreSQL
format()
has a built-in way to escape identifiers. Simpler than before:Works with a
VALUES
expression as well.db<>fiddle here
Old sqlfiddle
Major points
format()
orquote_ident()
to quote identifiers (automatically and only where necessary), thereby defending against SQL injection and simple syntax violations.This is necessary, even with your own table names!
search_path
setting a bare table name might otherwise resolve to another table of the same name in a different schema.EXECUTE
for dynamic DDL statements.USING
clause.RETURN OLD;
in the trigger function is required for a triggerBEFORE DELETE
. Details in the manual.You get the error message in your almost successful version because
OLD
is not visible insideEXECUTE
. And if you want to concatenate individual values of the decomposed row like you tried, you have to prepare the text representation of every single column withquote_literal()
to guarantee valid syntax. You would also have to know column names beforehand to handle them or query the system catalogs - which stands against your idea of having a simple, dynamic trigger function ...My solution avoids all these complications. Also simplified a bit.
PostgreSQL 9.0 or earlier
format()
is not available, yet, so:Related:
我只是偶然发现了这个,因为我正在寻找动态
INSTEAD OF DELETE
触发器。为了感谢您提出的问题和答案,我将发布我的 Postgres 9.3 解决方案。I just stumbled upon this because I was searching for a dynamic
INSTEAD OF DELETE
trigger. As a thank you for the question and answers I'll post my solution for Postgres 9.3.