Thread: EXECUTE in trigger functions.
Hi, Is there a way of using EXECUTE in trigger functions to to do something like: CREATE OR REPLACE FUNCTION insert_trigger() RETURNS trigger AS $BODY$ BEGIN EXECUTE('INSERT INTO public_partitions.table_' || date_part('year',NEW.eventdate)::VarChar || lpad(date_part('month',NEW.eventdate)::Varchar,2,'0') || lpad(date_part('day',NEW.eventdate)::Varchar,2,'0') || ' VALUES (NEW.*)'); RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' This would obviously be very useful for partitioning - however if I try this I get: SQL state: 42601 Context: SQL statement "INSERT INTO public_partitions.summary_20080101 VALUES (NEW.*)" PL/pgSQL function "insert_trigger" line 2 at EXECUTE statement Thanks, David.
David Brain wrote: > Is there a way of using EXECUTE in trigger functions to to do something > like: > > CREATE OR REPLACE FUNCTION insert_trigger() > RETURNS trigger AS > $BODY$ > BEGIN > EXECUTE('INSERT INTO public_partitions.table_' > || date_part('year',NEW.eventdate)::VarChar > || lpad(date_part('month',NEW.eventdate)::Varchar,2,'0') > || lpad(date_part('day',NEW.eventdate)::Varchar,2,'0') > || ' VALUES (NEW.*)'); > RETURN NULL; > END; > $BODY$ > LANGUAGE 'plpgsql' > > This would obviously be very useful for partitioning - however if I try > this I get: Execute does execute a given string of SQL. To my knowledge there is no way you can pass new.* to that statement. What should work is to use prepare and bind all fields of new.* separately. http://www.postgresql.org/docs/current/static/sql-prepare.html -- Best regards, Hannes Dorbath