pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: gntrs@hotmail.com
To: pgsql-sql@postgresql.org
Subject: Re: Help with INSERT into 2 tables
Date: Fri, 16 Nov 2001 01:13:25 -0500
Message-ID: <E164cFl-0003nB-00@xyzzy.lan.internal> (raw)
In-Reply-To: <558f8e45.0111130542.1e617f99@posting.google.com>
References: <558f8e45.0111130542.1e617f99@posting.google.com>

-----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-----



view thread (6+ messages)  latest in thread

Message-ID: <E164cFl-0003nB-00@xyzzy.lan.internal>
Permalink:  ../E164cFl-0003nB-00@xyzzy.lan.internal/
Also on:    postgresql.org/message-id/E164cFl-0003nB-00@xyzzy.lan.internal

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: drew@xyzzy.dhs.org, gntrs@hotmail.com
  Subject: Re: Help with INSERT into 2 tables
  In-Reply-To: <E164cFl-0003nB-00@xyzzy.lan.internal>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox