PostgreSQL 9.3 trigger function to insert into table with parameterized name

Question

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();

NEW isn't recognised, because EXECUTE cannot refer to outer variables. Try using the USING clause. postgresql.org/docs/9.3/static/… — pozs
– pozs, Commented Apr 23, 2014 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
– David Willis, Commented Apr 23, 2014 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
– David Willis, Commented Apr 23, 2014 at 15:18

Erwin Brandstetter · Accepted Answer · 2022-03-30 00:56:37Z

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.

edited Mar 30, 2022 at 0:56

answered Apr 23, 2014 at 15:58

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Willis Over a year ago

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.

Erwin Brandstetter Over a year ago

@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. Another related answer here.

David Willis Over a year ago

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!

Collectives™ on Stack Overflow

PostgreSQL 9.3 trigger function to insert into table with parameterized name

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related