Rules triggered by rules

Поиск
Список
Период
Сортировка
От Gregory Seidman
Тема Rules triggered by rules
Дата
Msg-id 20030110224012.GA1153@cs.brown.edu
обсуждение исходный текст
Список 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 по дате отправления:

Предыдущее
От: Philip Hallstrom
Дата:
Сообщение: Re: Demo System...
Следующее
От: Tom Lane
Дата:
Сообщение: Re: SCO 5.0.4 Compilation