Re: new and old not available in rule subselects?

Egil Möller wrote:
> It seems like you can not use the magical relations new and old in
> sub-selects in rules. Why is this, or is there something I haven't
> understood correctly about rules and new and old?
>
> create table "user" (
> username varchar,
> created timestamp default now(),
> detstroyed timestamp default null);
>
> create table users (
> time timestamp,
> users bigint);
>
> create rule user_insert_count as
> on update to "user"
> do also
> select
> new.created, (select count(*) from new where destroyed is null)
> into users
> from new;
>
> ERROR: relation "*NEW*" does not exist
>
> Thanks in advance,
> Egil

select count(*) from new ??? - new refers to a record not a table name

Try

create rule user_insert_count as
on update to "user"
do also
insert into users (time,users) values (
new.created, (select count(*) from "user" where destroyed is null));

--

Shane Ambler
pgSQL(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz