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
