Skip site navigation (1) Skip section navigation (2)

Re: Conditional rule?

From: JanWieck(at)t-online(dot)de (Jan Wieck)
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: =?iso-8859-1?B?QW5kcukgTuZzcw==?= <andre(dot)nass(at)student(dot)uib(dot)no>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Conditional rule?
Date: 2000-07-27 22:16:42
Message-ID: 200007272216.AAA28690@hot.jw.home (view raw or flat)
Thread:
Lists: pgsql-sql
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 #



In response to

Responses

pgsql-sql by date

Next:From: Bernie HuangDate: 2000-07-27 22:19:35
Subject: BLOBs
Previous:From: dannyDate: 2000-07-27 21:34:18
Subject: how to r/w blob field in php

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group