[SQL] ON INSERT view rule

[Anthony Chavez]

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

-- 
Anthony Chavez http://anthonychavez.org/
mailto:[EMAIL PROTECTED] jabber:[EMAIL PROTECTED]

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 = a.id
   LEFT OUTER JOIN
   junctions
   ON parent_table = 'address_lines'
  AND parent_id = al.id
  AND child_table = 'display_orderings'
   LEFT OUTER JOIN
   display_orderings o
   ON o.id = 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 = NEW.ordering
  FROM address_lines_id_seq
   INNER JOIN
   junctions
   ON parent_table = 'address_lines'
  AND parent_id = last_value
  AND child_table = 'display_orderings'
 WHERE ordering <> NEW.ordering
   AND display_orderings.id = child_id;


pgpSI6vATjFFf.pgp
Description: PGP signature

Re: [SQL] ON INSERT view rule

[Anthony Chavez]

Anthony Chavez <[EMAIL PROTECTED]> writes:

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

Oops, forgot to mention two things:

1. The addresses_address_lines view assumes that a row already exists
   in the addresses relation because that relation has some NOT NULL
   attributes that lack defaults.  Hence, there is no insert_addresses
   rule.  I suppose I should create one, but choosing a default value
   for some of the foreign keys in that relation would be difficult.

2. I have an AFTER INSERT trigger function on the addresses relation
   that inserts a default display_orderings tuple (with ordering = 0)
   and sets up the association in the junctions table.  Hence the use
   of UPDATE in the insert_display_orderings rule.

Cheers!

-- 
Anthony Chavez http://anthonychavez.org/
mailto:[EMAIL PROTECTED] jabber:[EMAIL PROTECTED]


pgp83LIixWmPl.pgp
Description: PGP signature