From: JanWieck@t-online.de
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: André Næss?us-ascii?Q?=3D?= <andre.nass@student.uib.no>
Cc: pgsql-sql@postgresql.org
Subject: Re: Conditional rule?
Date: Fri, 28 Jul 2000 00:16:42 +0200 (MEST)
Message-ID: <200007272216.AAA28690@hot.jw.home> (raw)
In-Reply-To: <8320.964712170@sss.pgh.pa.us>
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 #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: JanWieck@t-online.de, tgl@sss.pgh.pa.us, andre.nass@student.uib.no
Subject: Re: Conditional rule?
In-Reply-To: <200007272216.AAA28690@hot.jw.home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox