Rules triggered by rules - Mailing list pgsql-general

From Gregory Seidman
Subject Rules triggered by rules
Date
Msg-id 20030110224012.GA1153@cs.brown.edu
Whole thread Raw
List 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


pgsql-general by date:

Previous
From: Philip Hallstrom
Date:
Subject: Re: Demo System...
Next
From: Tom Lane
Date:
Subject: Re: SCO 5.0.4 Compilation