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

Re: Conditional rule?

From: André Næss <andre(dot)nass(at)student(dot)uib(dot)no>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Conditional rule?
Date: 2000-07-27 17:21:55
Message-ID: 005801bff7ef$296d5a20$74b5f8c2@wkst6 (view raw or flat)
Thread:
Lists: pgsql-sql
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(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.
>
> regards, tom lane
>


In response to

Responses

pgsql-sql by date

Next:From: André NæssDate: 2000-07-27 18:01:00
Subject: Re: Conditional rule?
Previous:From: Tom LaneDate: 2000-07-27 15:36:10
Subject: Re: Conditional rule?

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