3

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.

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

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!

The error:

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

My function:

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;

My trigger:

CREATE TRIGGER log_roll_test
BEFORE INSERT ON log FOR EACH ROW
EXECUTE PROCEDURE log_roll_test();
Johan
  • 74,508
  • 24
  • 191
  • 319
David Willis
  • 183
  • 3
  • 8
  • 1
    `NEW` isn't recognised, because `EXECUTE` cannot refer to outer variables. Try using the `USING` clause. http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN – pozs Apr 23 '14 at 14:28
  • Thanks! I tried that already and it doesn't work. It passes the NEW record to the statement, and the EXECUTE statement treats that as a string. I.e., '(value1, value2, value3,,,)'. Postgres doesn't like the commas next to each other, and it doesn't like quotes around the values. I would need to do a regexp_replace to modify the string, and I'd really like to avoid that for performance reasons. – David Willis Apr 23 '14 at 15:17
  • I've tried about everything, BTW. Pretty close to just giving up and writing a trigger with a CASE WHEN statement (53 lines) and writing a pgScript to create 53 separate trigger functions. – David Willis Apr 23 '14 at 15:18

1 Answers1

6
CREATE FUNCTION log_roll_test()
  RETURNS trigger
  LANGUAGE plpgsql 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$;

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 values in the USING clause.

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you Erwin!!! Simple and elegant solution. I'm gonna use %I.%I in the format() statement so I can add a schema qualifier. It works great and will make the trigger function work for multiple log tables. – David Willis Apr 24 '14 at 21:07
  • @DavidWillis: Where does the schema name come from? For a static value `%s` is good enough. `%I` is only necessary to sanitize dynamic values. If schema and tablename are dynamic, [a cast to `regclass` would be an alternative](http://stackoverflow.com/questions/10705616/table-name-as-a-postgresql-function-parameter/10711349#10711349). [Another related answer here.](http://stackoverflow.com/questions/22656716/format-specifier-for-integer-variables-in-format-for-execute/22675836#22675836) – Erwin Brandstetter Apr 24 '14 at 22:22
  • Good links. The schema name is static. I ended up casting tg_table_name to regclass. Goal was to get one simple trigger function working for multiple log tables, and I think I'm there. Thanks for the help! – David Willis Apr 26 '14 at 04:04