From: | Gregory Seidman <gss+pg(at)cs(dot)brown(dot)edu> |
---|---|
To: | PostgreSQL general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Rules triggered by rules |
Date: | 2003-01-10 22:40:12 |
Message-ID: | 20030110224012.GA1153@cs.brown.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Consider the following (contrived) tables, view, and rule:
CREATE TABLE People (
uid SERIAL not null,
first varchar(255) not null,
last varchar(255) not null,
primary key (uid)
);
CREATE TABLE Attributes (
uid integer not null REFERENCES People(uid),
tattooed boolean not null default false,
alive boolean not null default true,
primary key (uid)
);
CREATE RULE AttributeRow AS ON INSERT TO People DO (
INSERT INTO Attributes(uid)
VALUES (COALESCE(NEW.uid, currval('people_uid_seq')));
);
CREATE VIEW LongPeople AS (
SELECT p.*, a.tattooed, a.alive
FROM People AS p JOIN Attributes AS a ON p.uid = a.uid
);
I would like to add a rule for inserting into the view. The question is
whether or not the AttributeRow rule will be triggered. Actually, a better
question is probably when will the AttributeRow rule be triggered?
If I can figure out how to make AttributeRow only insert if a row with the
right uid doesn't exists, I think I can get around the issue by inserting
into the Attributes table first, but I'm not entirely sure. I'm also not
sure how reasonable/efficient the following modified AttributeRow rule is
(I'm pretty sure of its correctness):
CREATE RULE AttributeRow AS ON INSERT TO People DO (
INSERT INTO Attributes(uid)
SELECT COALESCE(NEW.uid, currval('people_uid_seq'))
WHERE NOT EXISTS (
SELECT * FROM Attributes
WHERE uid = COALESCE(NEW.uid, currval('people_uid_seq'))
)
);
So those are the two questions:
1) When will AttributeRow be executed when triggered by an ON UPDATE DO
INSTEAD rule for LongPeople?
2) Is the modified AttributeRow rule above reasonable or is there a better
way?
--Greg
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-01-10 23:52:11 | Re: SCO 5.0.4 Compilation |
Previous Message | Philip Hallstrom | 2003-01-10 22:24:48 | Re: Demo System... |