Re: new and old not available in rule subselects? - Mailing list pgsql-general

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@007Marketing.com

Get Sheeky @ http://Sheeky.Biz