From: André Næss <andre.nass@student.uib.no>
To: pgsql-sql@postgresql.org
Subject: Re: Conditional rule?
Date: Thu, 27 Jul 2000 20:01:00 +0200
Message-ID: <00aa01bff7f4$9f623e80$74b5f8c2@wkst6> (raw)
References: <87k8e78yg9.fsf@matt.w80.math-hat.com>
<003601bff7d9$18cdd610$74b5f8c2@wkst6>
<8320.964712170@sss.pgh.pa.us>
<005801bff7ef$296d5a20$74b5f8c2@wkst6>
Ooops... seems I had a rule tied to my news table which caused the
malfunction, sorry if I wasted anyone's time :(
André Næss
----- Original Message -----
From: "André Næss" <andre.nass@student.uib.no>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, July 27, 2000 7:21 PM
Subject: Re: [SQL] Conditional rule?
> Hm... I'm a true newbie when it comes to plpgsql and triggers, but I
looked
> through some texts and managed to come up with the following rather odd
(to
> me at least) behaviour:
>
> *****
>
> create table news (
> id serial,
> title varchar(50),
> time timestamp
> )
>
> create table news_un (
> news_id int
> )
>
> Table "news_un"
> Attribute | Type | Modifier
> -----------+---------+----------
> news_id | integer |
>
> create function setpublish() returns opaque as '
> begin
> insert into news_un select news_id_seq.last_value where new.time is
not
> null;
> return null;
> end;
> '
> language 'plpgsql';
>
> create trigger newstrigger after insert on news for each row execute
> procedure setpublish();
>
> *****
> Attempting to do an insert to news:
>
> testruledb=# insert into news (title, time) values('Test', now());
> INSERT 24028 1
> testruledb=# select * from news; select * from news_un;
> id | title | time
> ----+-------+------------------------
> 48 | Test | 2000-07-27 19:20:24+02
> (1 row)
>
> news_id
> ---------
> 47
> 48
> (2 rows)
>
> I also tried setting time to null:
>
> testruledb=# insert into news (title) values('Test2');
> INSERT 24031 1
> testruledb=# select * from news; select * from news_un;
> id | title | time
> ----+-------+------
> 50 | Test2 |
> (1 row)
>
> news_id
> ---------
> 49
> (1 row)
>
> There's obviously something about triggers and functions I don't
understand,
> any help would be greatly appreciated.
>
> Thanks
>
> André Næss
>
>
> ----- Original Message -----
> > "=?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.
> >
> > regards, tom lane
> >
>
>
>
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: andre.nass@student.uib.no
Subject: Re: Conditional rule?
In-Reply-To: <00aa01bff7f4$9f623e80$74b5f8c2@wkst6>
* 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