Skip site navigation (1) Skip section navigation (2)

Re: EXECUTE in trigger functions.

From: Hannes Dorbath <light(at)theendofthetunnel(dot)de>
To: David Brain <dbrain(at)bandwidth(dot)com>
Subject: Re: EXECUTE in trigger functions.
Date: 2008-10-27 18:37:36
Message-ID: 49060A70.1060907@theendofthetunnel.de (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Andreas JochemDate: 2008-10-27 18:38:22
Subject: Re: Execute Shell script after insert
Previous:From: Scott MarloweDate: 2008-10-27 18:35:45
Subject: Re: Execute Shell script after insert

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group