Received: from femail24.sdc1.sfba.home.com (femail24.sdc1.sfba.home.com [24.0.95.149]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAG6DWm78025 for ; Fri, 16 Nov 2001 01:13:32 -0500 (EST) (envelope-from drew@xyzzy.dhs.org) Received: from xyzzy.lan.internal ([24.226.81.186]) by femail24.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with ESMTP id <20011116061331.XVGR731.femail24.sdc1.sfba.home.com@xyzzy.lan.internal>; Thu, 15 Nov 2001 22:13:31 -0800 Received: from localhost ([127.0.0.1] helo=there ident=drew) by xyzzy.lan.internal with smtp (Exim 3.32 #1 (Debian)) id 164cFl-0003nB-00; Fri, 16 Nov 2001 01:13:30 -0500 Content-Type: text/plain; charset="iso-8859-1" From: "Andrew G. Hammond" To: gntrs@hotmail.com (Gintas), pgsql-sql@postgresql.org Subject: Re: Help with INSERT into 2 tables Date: Fri, 16 Nov 2001 01:13:25 -0500 X-Mailer: KMail [version 1.3.2] References: <558f8e45.0111130542.1e617f99@posting.google.com> In-Reply-To: <558f8e45.0111130542.1e617f99@posting.google.com> MIME-Version: 1.0 Message-Id: Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id fAG6UTn78321 X-Archive-Number: 200111/234 X-Sequence-Number: 5616 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 CREATE TABLE a (id SERIAL PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE b (id SERIAL PRIMARY KEY, key_a INTEGER REFERENCES a (id) ON DELETE CASCADE, data TEXT); > I want to insert related records to both table. The problem is that > in order to insert record to the second table it's necessary to know > a.id Common question, and there's several possible answers. I'm going to break slightly from the norm though. Postgres has some cool features, and since we can stuff this logic into the database, we might just as well. So let's play with them! CREATE VIEW ab AS SELECT a.id, a.name, b.data FROM a,b WHERE a.id = b.key_a; CREATE FUNCTION ab_insert (text, text) RETURNS INTEGER AS ' DECLARE newid INTEGER; newname ALIAS FOR $1; newdata ALIAS FOR $2; BEGIN newid := nextval(''a_id_seq''); RAISE NOTICE ''newid is %'', newid; INSERT INTO a (id, name) VALUES (newid, newname); INSERT INTO b (key_a, data) VALUES (newid, newdata); RETURN newid; END;' LANGUAGE 'plpgsql'; CREATE RULE ab_ins_rule AS ON INSERT TO ab DO INSTEAD SELECT ab_insert(new.name, new.data); I'd really like to figure out how to write a function that doesn't return anything (a "procedure" for all you pedantic CS types). Oh well. - -- Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjv0roYACgkQCT73CrRXhLFd4wCeNvUf1sYztKvs0Xqq9cfcDy97 n/wAmwXdCCaxrKQ6oTbtqSyhJ2IhSExG =78uf -----END PGP SIGNATURE-----