Re: Partitionning + Trigger and Execute not working as expected

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Sylvain Mougenot <smougenot(at)sqli(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Partitionning + Trigger and Execute not working as expected
Date: 2011-11-10 01:44:38
Message-ID: CAK3UJRGB6NQ__MfCfiPi62WCsN5CKiXsgALZRzcuru_OisModw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

On Wed, Nov 9, 2011 at 4:39 PM, Sylvain Mougenot <smougenot(at)sqli(dot)com> wrote:
> As I mentioned before, your code works on special cases (insert with all the
> columns) and those are very few cases.
> Try this
> CREATE TABLE foo (a int, b int);
> CREATE TABLE job_2011_11 (c int, d int);
>
> CREATE OR REPLACE FUNCTION job_insert_trigger()
> RETURNS TRIGGER AS
> $BODY$
> DECLARE
>        currentTableName character varying := 'job_' || '2011_11';
> BEGIN
>        EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*);
>        RETURN NULL;
> END;
> $BODY$
>  LANGUAGE plpgsql VOLATILE
>  COST 100;
>
> CREATE TRIGGER job_insert_trg BEFORE INSERT ON foo
>  FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger();
> INSERT INTO foo (a, b) VALUES (1, 2);
> INSERT INTO foo (a) VALUES (10);
> ERROR:
> LINE 1: INSERT INTO job_2011_11 values (10,)
>                                            ^
> QUERY:  INSERT INTO job_2011_11 values (10,)
> CONTEXT:  PL/pgSQL function "job_insert_trigger" line 5 at instruction
> EXECUTE

Oh, I see what you're on about now. Sounds like you're looking for the
USING clause of EXECUTE. Try this:

CREATE OR REPLACE FUNCTION job_insert_trigger()
RETURNS TRIGGER AS
$BODY$
DECLARE
currentTableName character varying := 'job_' || '2011_11';
BEGIN
EXECUTE 'INSERT INTO '|| currentTableName || ' (c, d) VALUES ($1, $2)'
USING NEW.a, NEW.b;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Josh

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message tlund79 2011-11-10 11:14:10 Returning data from multiple functions
Previous Message Sylvain Mougenot 2011-11-09 21:39:58 Re: Partitionning + Trigger and Execute not working as expected