pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedON INSERT view rule
2+ messages / 1 participants
[nested] [flat]
* ON INSERT view rule
@ 2006-07-11 10:53 Anthony Chavez <acc@anthonychavez.org>
2006-07-11 12:56 ` Re: ON INSERT view rule Anthony Chavez <acc@anthonychavez.org>
0 siblings, 1 reply; 2+ messages in thread
From: Anthony Chavez @ 2006-07-11 10:53 UTC (permalink / raw)
To: 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@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 = 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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ON INSERT view rule
2006-07-11 10:53 ON INSERT view rule Anthony Chavez <acc@anthonychavez.org>
@ 2006-07-11 12:56 ` Anthony Chavez <acc@anthonychavez.org>
0 siblings, 0 replies; 2+ messages in thread
From: Anthony Chavez @ 2006-07-11 12:56 UTC (permalink / raw)
To: pgsql-sql
Anthony Chavez <acc@anthonychavez.org> 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:acc@anthonychavez.org jabber:acc@jabber.anthonychavez.org
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-07-11 12:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-11 10:53 ON INSERT view rule Anthony Chavez <acc@anthonychavez.org>
2006-07-11 12:56 ` Anthony Chavez <acc@anthonychavez.org>
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