Re: INSERT RETURNING and partitioning - Mailing list pgsql-general

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;
END;
$BODY$
  LANGUAGE 'plpgsql';


CREATE TRIGGER master_insert_trigger
  BEFORE INSERT
  ON support.master
  FOR EACH ROW
  EXECUTE PROCEDURE support.master_insert();



Regards,
Paolo


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