From: | "marc ratun" <marc_ratun(at)hotmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | INSERT INTO VIEW - Replacement |
Date: | 2004-11-09 10:41:54 |
Message-ID: | BAY13-F1881TbdkSmLA000003ac@hotmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I've this data model:
CREATE SEQUENCE a_seq START 1;
CREATE SEQUENCE b_seq START 1;
CREATE TABLE a (
aid integer NOT NULL PRIMARY KEY,
aval character varying (255) NOT NULL
);
INSERT INTO a (select nextval('a_seq'),'a1');
INSERT INTO a (select nextval('a_seq'),'a2');
CREATE TABLE b (
bid integer NOT NULL PRIMARY KEY,
bval character varying (255) NOT NULL
);
INSERT INTO b (select nextval('b_seq'),'b1');
INSERT INTO b (select nextval('b_seq'),'b2');
CREATE TABLE c (
cid integer NOT NULL,
aid integer REFERENCES a (aid),
bid integer REFERENCES b (bid),
cval character varying (255) NOT NULL,
PRIMARY KEY (cid)
);
CREATE VIEW myview AS SELECT cid,aval,bval,cval FROM c INNER JOIN a ON
(a.aid=c.aid) INNER JOIN b ON (b.bid=c.bid);
Now I'd like to insert a row into "c" and if necessary
simultaneously create the referenced rows in
the referenced tables:
INSERT INTO myview VALUES (10,'a3','b1','c1');
(Here a row in "a" with aval 'a3' should be
created)
INSERT INTO myview VALUES (20,'a1','b2','c2');
(here a row in "a" with aval 'a1' should not
be created but referenced.)
(Same for "b").
"Insert into view" would't work, how could I do
my INSERTS efficiently in postgres without
having eache time an extra query which asks
whether there already is a row in "a" which
could be referenced to.
ie mache ich das
Thanks!
Marc
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2004-11-09 13:55:01 | Re: A transaction in transaction? Possible? |
Previous Message | Andras Kutrovics | 2004-11-09 09:16:22 | Re: Simple SQL Question |