Re: INSERT RETURNING and partitioning

Hi Jan,

----Messaggio originale----
Da: asche(at)me(dot)com
Data: 21/07/2010 10.17
A: "pdovera(at)tiscali(dot)it"<pdovera(at)tiscali(dot)it>
Cc: "pgsql-general(at)postgresql(dot)org"<pgsql-general(at)postgresql(dot)org>
Ogg: Re: [GENERAL] INSERT RETURNING and partitioning

hi,

On Jul 21, 2010, at 10:02, "pdovera(at)tiscali(dot)it" <pdovera(at)tiscali(dot)it>
wrote:

> Hi,
> I'm testing the system with these two insert commands:
>
> 1) this command returns an empty result set:
> insert into support.master (a) VALUES (2) RETURNING seq;
>
> 2) this command returns correctly the seq (serial) value into result
> set:
> insert into support.partitionB (a) VALUES (2) RETURNING seq;
>
> I'm doing something wrong?
>
> I'm using the following DDL to create the partitioning tables,
trigger
> and so on ...
>
> create table support.master(
> seq serial,
> a INTEGER PRIMARY KEY
> );
>
> create table support.partitionA(
> CHECK (a = 1)
> ) INHERITS (support.master);
>
> create table support.partitionB(
> CHECK (a = 2)
> ) INHERITS (support.master);
>
> create table support.partitionC(
> CHECK (a = 3)
> ) INHERITS (support.master);
>
> create table support.partitionD(
> CHECK (a = 4)
> ) INHERITS (support.master);
>
> CREATE OR REPLACE FUNCTION support.master_insert()
> RETURNS trigger AS
> $BODY$
> BEGIN
> IF ( NEW.a = 1) THEN INSERT INTO support.partitionA VALUES (NEW.
> *);
> ELSIF ( NEW.a = 2) THEN INSERT INTO support.partitionB VALUES (NEW.
*);
> ELSIF ( NEW.a = 3) THEN INSERT INTO support.partitionC VALUES (NEW.
*);
> ELSIF ( NEW.a = 4) THEN INSERT INTO support.partitionD VALUES (NEW.
*);
> ELSE RAISE EXCEPTION 'A (%)is out of range ',NEW.a;
> END IF;
> RETURN NULL;

a trigger for insert should return NEW, no? ;-)

change that and it will work.

regards, jan

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Your solution does not works completely .. it duplicates the rows

I've made the change, now RETURNINGon support.master works BUT the
rows are duplicated.
The row are written into support.master and support.partition?
and this in not good for me ...

regards,
Paolo

PS: sorry for previous direct email

SCARICA TISCALI WIPHONE: parla e invia SMS gratis dal tuo cellulare.
http://wiphone.tiscali.it