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
