Thread: new and old not available in rule subselects?

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

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

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

In the description in the manual, new and old are described as
relations... Also, isn't the point that the query is rewritten into the
new query with rules, thus if you update more than one row, new should
be all the updated rows, not just one at a time (a rule is not a
trigger)?

>
> 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));

I tried that, but "user" only contains the old (non-updated) records,
so the record count will be off by one in either direction...

Regards,
Egil

redhog wrote:
>> select count(*) from new ??? - new refers to a record not a table name
>
> In the description in the manual, new and old are described as
> relations... Also, isn't the point that the query is rewritten into the
> new query with rules, thus if you update more than one row, new should
> be all the updated rows, not just one at a time (a rule is not a
> trigger)?
>
>> 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));
>
> I tried that, but "user" only contains the old (non-updated) records,
> so the record count will be off by one in either direction...
>

In this case your not rewriting the query you are DOing ALSO - the
command you add is run after the update that matches the rule criteria
(all updates to "user" in this case) - if you update 4 rows then the
rule is run 4 times after the 4 rows have been updated not as each row
is updated, which means you will always get the same count(*) number for
all 4 updates.


The use of new that you are thinking of is -

CREATE OR REPLACE RULE user_insert_count AS
     ON UPDATE TO "user" DO  INSERT INTO users ("time", users)
   VALUES (new.created, ( SELECT count(*)
            FROM "user"
           WHERE new.destroyed IS NULL));

But that won't give the result you want - new and old are versions of
the row being updated and the WHERE new.destroyed IS NULL will only
compare the new.destroyed value of the current row not the new value of
each row in the table. You will either get the same as SELECT count(*)
FROM "user" or you will get 0

You may want to try -
CREATE RULE user_insert_count AS
     ON UPDATE DO INSTEAD
       UPDATE "user" ...
       INSERT INTO users ...

But I think you may need to use a trigger to get the results you want.


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz