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