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.timeis 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 -----
> "André Næss" <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
>
