PostgreSQL 9.3触发函数以参数化名称插入表 [英] PostgreSQL 9.3 trigger function to insert into table with parameterized name

问题描述

我正在尝试在Postgres中动态分区日志条目.我有53个子表(每个星期的日志条目价值1个子表)，并希望使用触发器将INSERT路由到子表.

I'm trying to dynamically partition log entries in Postgres. I have 53 child tables (1 for each week's worth of log entries), and would like to route INSERTs to a child table using a trigger.

我使用INSERT INTO log5 VALUES (NEW.*)运行该函数，并且有效.

I run the function with INSERT INTO log5 VALUES (NEW.*), and it works.

我改用EXECUTE语句运行该函数，但失败.在EXECUTE语句中，它会将NEW识别为表名，而不是传递给触发函数的变量.关于如何解决的任何想法?谢谢！

I run the function with the EXECUTE statement instead, and it fails. Within the EXECUTE statement, it's recognizing NEW as a table name and not a variable passed to the trigger function. Any ideas on how to fix? Thanks!

错误:

查询:插入日志5个值(新.*)
上下文:EXECUTE语句中的PL/pgSQL函数log_roll_test()第6行
错误:表"new"的SQL状态缺少FROM子句条目:42P01

QUERY: INSERT INTO log5 VALUES (NEW.*)
CONTEXT: PL/pgSQL function log_roll_test() line 6 at EXECUTE statement
ERROR: missing FROM-clause entry for table "new" SQL state: 42P01

我的功能:

CREATE FUNCTION log_roll_test() RETURNS trigger AS $body$
DECLARE t text;
BEGIN
    t := 'log' || extract(week FROM NEW.updt_ts); --child table name
    --INSERT INTO log5 VALUES (NEW.*);
    EXECUTE format('INSERT INTO %I VALUES (NEW.*);', t);
    RETURN NULL;
END;
$body$ LANGUAGE plpgsql;

我的触发器:

CREATE TRIGGER log_roll_test
BEFORE INSERT ON log FOR EACH ROW
EXECUTE PROCEDURE log_roll_test();

推荐答案

CREATE FUNCTION log_roll_test()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I SELECT ($1).*'
                , to_char(NEW.updt_ts, '"log"WW'))   -- child table name
   USING NEW;
   RETURN NULL;
END
$func$ LANGUAGE plpgsql;

您不能在查询字符串中引用NEW. NEW在功能主体中可见，但在EXECUTE环境中不可见.最好的解决方案是传递USING子句中的值.

You cannot reference NEW inside the query string. NEW is visible in the function body, but not inside EXECUTE environment. The best solution is to pass the values in the USING clause.

我还用等效的to_char(NEW.updt_ts, '"log"WW')代替了表名. to_char() 在这里更快，更简单.

I also substituted the equivalent to_char(NEW.updt_ts, '"log"WW') for the table name. to_char() is faster and simpler here.

这篇关于PostgreSQL 9.3触发函数以参数化名称插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！