Search Postgresql Archives

Re: INSERT RETURNING and partitioning

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Tom

----Messaggio originale----
Da: thombrown@xxxxxxxxx
Data: 21/07/2010 10.38
A: "Jan Otto"<asche@xxxxxx>
Cc: "pdovera@xxxxxxxxxx"<pdovera@xxxxxxxxxx>, "pgsql-
general@xxxxxxxxxxxxxx"<pgsql-general@xxxxxxxxxxxxxx>
Ogg: Re:  INSERT RETURNING and partitioning

On 21 July 2010 09:17, Jan Otto <asche@xxxxxx> wrote:
> hi,
>
> On Jul 21, 2010, at 10:02, "pdovera@xxxxxxxxxx" <pdovera@xxxxxxxxxx> 
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

I prefer to avoid duplicated rows because that is not the idea of 
partitioning

Paolo


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


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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux