Re: Conditional rule?

Tom Lane wrote:
> "André Næss" <andre.nass@student.uib.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_unpublishedVALUES (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_unpublishedWHERE 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@Yahoo.com #