From: JanWieck@t-online.de (Jan Wieck)
Message-Id: <200007272216.AAA28690@hot.jw.home>
Subject: Re: Conditional rule?
In-Reply-To: <8320.964712170@sss.pgh.pa.us> from Tom Lane at "Jul 27,
	2000 11:36:10 am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 28 Jul 2000 00:16:42 +0200 (MEST)
CC: =?us-ascii?Q?=3D=3Fiso=2D8859=2D1=3FB=3FQW5kcukgTuZzcw=3D=3D=3F?=
	=?us-ascii?Q?=3D?= <andre.nass@student.uib.no>, pgsql-sql@postgresql.org
Reply-To: Jan Wieck <JanWieck@Yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> "=?iso-8859-1?B?QW5kcukgTuZzcw==?=" <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_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@Yahoo.com #