Insert rule and default values for PK

From: KÖPFERL Robert <robert(dot)koepferl(at)sonorys(dot)at>
To: pgsql-sql(at)postgresql(dot)org
Subject: Insert rule and default values for PK
Date: 2005-06-29 12:53:20
Message-ID: ED4E30DD9C43D5118DFB00508BBBA76EB167B0@neptun.sonorys.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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) REFERENCES tbla (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.

Browse pgsql-sql by date

  From Date Subject
Next Message Martín Marqués 2005-06-29 13:21:10 Re: ENUM like data type
Previous Message KÖPFERL Robert 2005-06-29 12:40:08 Re: ENUM like data type