Rules triggered by rules

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

Browse pgsql-general by date

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