Re: Conditional rule?

Tom Lane wrote:
> "=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <andre(dot)nass(at)student(dot)uib(dot)no> writes:
> > I wish to make rule looking something like this:
> > create rule newsrule as
> > on insert to news do
> > if new.publishtime is not null insert into news_unpublished
> > values(new.id);
>
> > I.e. "On an insert to news, if new.publish is not null, insert the new
> > post's id into news_unpublished.
>
> What you want here is a trigger, not a rule. The closest you could come
> with a rule is to copy *all* unpublished ids into news_unpublished each
> time something got inserted into news. There are applications for that
> sort of thing, but this ain't it. See the trigger examples in the
> plpgsql or pltcl sections of the manual.

No. The rule

CREATE RULE newsrule AS ON INSERT TO news
WHERE new.publishtime NOTNULL DO
INSERT INTO news_unpublished VALUES (new.id);

should do the job perfectly. Maybe you want to have the
following rules too:

CREATE RULE newsrule2 AS ON UPDATE TO news
WHERE old.publishtime ISNULL AND new.publishtime NOTNULL DO
INSERT INTO news_unpublished VALUES (new.id);

CREATE RULE newsrule3 AS ON UPDATE TO news
WHERE old.publishtime NOTNULL AND new.publishtime ISNULL DO
DELETE FROM news_unpublished WHERE news_unpublished.id = old.id;

CREATE RULE newsrule4 AS ON DELETE TO news
WHERE old.publishtime NOTNULL DO
DELETE FROM news_unpublished WHERE news_unpublished.id = old.id;

With these four rules, all the inserts and deletes are done
automatically.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #