From: "marc ratun" <marc_ratun@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: INSERT INTO VIEW - Replacement
Date: Tue, 09 Nov 2004 10:41:54 +0000
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <BAY13-F1881TbdkSmLA000003ac@hotmail.com>

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/