From: | Anthony Chavez <acc(at)anthonychavez(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | ON INSERT view rule |
Date: | 2006-07-11 10:53:40 |
Message-ID: | m23bd8h0p7.fsf@pegasos.aegaeum.anthonychavez.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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:acc(at)anthonychavez(dot)org jabber:acc(at)jabber(dot)anthonychavez(dot)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 = 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Penchalaiah P. | 2006-07-11 11:34:06 | above the date functionssssssss |
Previous Message | Aaron Bono | 2006-07-11 04:11:16 | Re: Can function results be used in WHERE? |