在触发器函数中使用动态表名进行 INSERT

发布于 2024-12-12 10:59:15 字数 903 浏览 0 评论 0原文

我不确定如何实现类似以下内容:

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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

七颜 2024-12-19 10:59:16

现代 PostgreSQL

format() 有一种内置的方法来转义标识符。比以前更简单:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
                , TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow')
   USING OLD;

   RETURN OLD;
END
$func$;

使用 VALUES 表达也是如此。

db<>fiddle 此处
sqlfiddle

要点

您收到错误消息< /strong> 在您几乎成功的版本中,因为 OLDEXECUTE不可见。如果您想像您尝试的那样连接分解行的各个值,则必须使用 quote_literal() 准备每个列的文本表示形式,以保证语法有效。您还必须事先知道列名称来处理它们或查询系统目录 - 这违背了您拥有简单的动态触发函数的想法......

我的解决方案避免了所有这些复杂性。也简化了一点。

PostgreSQL 9.0 或更早

版本 format() 尚不可用,因此:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
    EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA)
                    || '.' || quote_ident(TG_TABLE_NAME || 'shadow')
                    || ' SELECT $1.*'
    USING OLD;

    RETURN OLD;
END
$func$;

相关:

Modern PostgreSQL

format() has a built-in way to escape identifiers. Simpler than before:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
                , TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow')
   USING OLD;

   RETURN OLD;
END
$func$;

Works with a VALUES expression as well.

db<>fiddle here
Old sqlfiddle

Major points

  • Use format() or quote_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!
  • Schema-qualify the table name. Depending on the current search_path setting a bare table name might otherwise resolve to another table of the same name in a different schema.
  • Use EXECUTE for dynamic DDL statements.
  • Pass values safely with the USING clause.
  • Consult the fine manual on Executing Dynamic Commands in plpgsql.
  • Note thatRETURN OLD; in the trigger function is required for a trigger BEFORE DELETE. Details in the manual.

You get the error message in your almost successful version because OLD is not visible inside EXECUTE. 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 with quote_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:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
    EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA)
                    || '.' || quote_ident(TG_TABLE_NAME || 'shadow')
                    || ' SELECT $1.*'
    USING OLD;

    RETURN OLD;
END
$func$;

Related:

↙厌世 2024-12-19 10:59:16

我只是偶然发现了这个,因为我正在寻找动态INSTEAD OF DELETE触发器。为了感谢您提出的问题和答案,我将发布我的 Postgres 9.3 解决方案。

CREATE OR REPLACE FUNCTION set_deleted_instead_of_delete()
RETURNS TRIGGER AS $
BEGIN
    EXECUTE format('UPDATE %I set deleted = now() WHERE id = $1.id', TG_TABLE_NAME)
    USING OLD;
    RETURN NULL;
END;
$ language plpgsql;

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.

CREATE OR REPLACE FUNCTION set_deleted_instead_of_delete()
RETURNS TRIGGER AS $
BEGIN
    EXECUTE format('UPDATE %I set deleted = now() WHERE id = $1.id', TG_TABLE_NAME)
    USING OLD;
    RETURN NULL;
END;
$ language plpgsql;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文