To: pgsql-sql@postgresql.org
From: Anthony Chavez <acc@anthonychavez.org>
Subject: ON INSERT view rule
Date: Tue, 11 Jul 2006 04:53:40 -0600
Lines: 76
Message-ID: <m23bd8h0p7.fsf@pegasos.aegaeum.anthonychavez.org>
Mime-Version: 1.0
Content-Type: multipart/signed; boundary="=-=-=";
	micalg=pgp-sha1; protocol="application/pgp-signature"
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.0.50 (darwin)
Cancel-Lock: sha1:tlvlVEpKrTnpBrNOA9KaUJPcy4U=

--=-=-=
Content-Transfer-Encoding: quoted-printable

Hello, pgsql-sql!

What I've got here are a couple of ON INSERT rules for a view.  The
second rule is what I'm concerned about.  I wrote it with PostgreSQL's
ACID compliance in mind, but can I trust it?

From=20what I gather, if I were to simply use NEW.address_line_id rather
than address_lines_id_seq.last_value, it would be replaced by
nextval(address_line_id_seq), so I'm trying to work around that.

If there is there a better way to do this, I'm all ears.  Would
lastval() work for me in this case?  Thanks!

=2D-=20
Anthony Chavez                                 http://anthonychavez.org/
mailto:acc@anthonychavez.org         jabber:acc@jabber.anthonychavez.org

CREATE OR REPLACE VIEW addresses_address_lines
AS
SELECT a.id AS address_id,
       al.id AS address_line_id,
       line,
       ordering
  FROM addresses a
       INNER JOIN
       address_lines al
       ON al.address_id =3D a.id
       LEFT OUTER JOIN
       junctions
       ON parent_table =3D 'address_lines'
          AND parent_id =3D al.id
          AND child_table =3D 'display_orderings'
       LEFT OUTER JOIN
       display_orderings o
       ON o.id =3D child_id;

CREATE OR REPLACE RULE insert_address_lines
AS ON INSERT
TO addresses_address_lines
DO INSTEAD
INSERT INTO address_lines (address_id, line)
VALUES (NEW.address_id, NEW.line);

CREATE OR REPLACE RULE insert_display_orderings
AS ON INSERT
TO addresses_address_lines
DO
UPDATE display_orderings
   SET ordering =3D NEW.ordering
  FROM address_lines_id_seq
       INNER JOIN
       junctions
       ON parent_table =3D 'address_lines'
          AND parent_id =3D last_value
          AND child_table =3D 'display_orderings'
 WHERE ordering <> NEW.ordering
       AND display_orderings.id =3D child_id;

--=-=-=
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.4 (Darwin)

iQEVAwUBRLODWPAIdTFWAbdTAQLHYQf+Kmb1ZLFSZaf05ITZJMOqMHBgpOCVYq6Q
Bu8+kK8d1ilN0BPeDqVrlH16kmv5EveZmyqpcM4Q8tBO8HK7hZqEwkkrTHJL1Sng
1qrVCQteS8EkQSpu24aHY72lFLzBullrmB59fZNFwstSjdjHo6as/b+NuqXnaboS
9HJjMQrqBV7igRHWLs07x1vPXuZ3gJXdV2kFbFR8rlSCyGYvmITrJGIcin868wqL
aCUVjgXRNvdGQlPwv+SwZZmbnNp4hrQMf2AYsSAtUQusVfzz8V+doYTBtWCM9V6h
DdGkDbTIjXS92f4iWj2b4v9rcLBRfJJtydvleqi6Lgw/ed7Eo2zCUQ==
=OP6p
-----END PGP SIGNATURE-----
--=-=-=--