Insert rule and default values for PK
От | KÖPFERL Robert |
---|---|
Тема | Insert rule and default values for PK |
Дата | |
Msg-id | ED4E30DD9C43D5118DFB00508BBBA76EB167B0@neptun.sonorys.at обсуждение исходный текст |
Список | pgsql-sql |
Hi, I've a prolem inserting records in a view using different ways. I want to insert either way, with PK given and without PK which should then be taken by its DEFAULT stanza: insert into a_and_b(a) values (537) # id not given, self assigned insert into a_and_b(x) values (true) # id not given, self assigned insert into a_and_b(x,id) values (true,55) # id given, take that For this example I created two tables. Note the Serial at tbla: CREATE TABLE tbla (id int4 NOT NULL,a int4,b varchar(12), CONSTRAINT tbla_pk PRIMARY KEY (id) ) CREATE TABLE tblb (id int4 NOT NULL,x bool, y timestamp,CONSTRAINT tblb_pk PRIMARY KEY (id),CONSTRAINT tblb_fk FOREIGN KEY (id) REFERENCEStbla (id) ON UPDATE CASCADE ON DELETE CASCADE ) My view is defined as to concatinate this 1:1 relation: CREATE OR REPLACE VIEW a_and_b AS SELECT tbla.id, tbla.a, tbla.b, tblb.x, tblb.y FROM tbla NATURAL LEFT OUTER JOIN tblb; In the first run I tried this insert rule. But it turned out that I can't insert rows, if I omit id CREATE OR REPLACE RULE a_b_insert AS ON INSERT TO a_and_b DO INSTEAD ( INSERT INTO tbla (id, a, b) VALUES (new.id, new.a,new.b); INSERT INTO tblb (id, x, y) VALUES (new.id, new.x, new.y);); So I had a try with that rule which already does what was intended. I can now choose of wheter I want to supply id of if not. However I get disturbed by the 'nextval' and 'curval'-functions. This is redundant with the tables definition. I don't like that. The other way round, leave all id out, helps for tbla (since a default is defined) but leaves tblb alone: CREATE OR REPLACE RULE a_b_insert AS ON INSERT TO a_and_b DO INSTEAD ( INSERT INTO tbla (id, a, b) VALUES (coalesce(new.id,nextval('public.tbla_id_seq'::text)),new.a, new.b);INSERT INTO tblb (id, x, y) VALUES (coalesce(new.id,currval('public.tbla_id_seq'::text)), new.x, new.y); ); What would you do? What is the postgres way of solving this problem.
В списке pgsql-sql по дате отправления: