current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 2 down vote favorite

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();
share|improve this question
1  
NEW isn't recognised, because EXECUTE cannot refer to outer variables. Try using the USING clause. postgresql.org/docs/9.3/static/… – 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
up vote 3 down vote accepted 
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;

VALUES expects a row type, that's why you can simply supply the row without unpacking it.

Either way, 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.

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

share|improve this answer
    
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. Another related answer here. – 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 4:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.