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