Re: INSERT RETURNING and partitioning

On 21 July 2010 09:17, Jan Otto <asche(at)me(dot)com> wrote:
> 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
>

Yes, Jan's right. You're effectively overriding the return values with NULL.

Although I think I know why you're doing it, because you want to
redirect the value to the child table so that it doesn't get inserted
into the parent table as that would cause duplicate values being
displayed in the parent table.

Unfortunately, you can't use RULEs as an alternative as they won't
allow returning values if they have conditions on them.

Thom