Re: 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