Hello,
I realised what I wrote in my last message was really confused.
You are absolutely right, I try to describe the whole situation.
1) There is a table which stores the goods of the vendors:
=#CREATE TABLE device (device_id serial, vendor_id integer, ... and some other columns);
2) there is another table (named stat) to store search hits on each vendor in daily distribution which equal the sum
ofsearch hits of their devices.
Therefore i created this table as follows:
=# CREATE TABLE stat (vendor_id integer, c_date date, c_num integer);
The column c_num is for storing the total count of hits a day.
3) I created a rule to avoid the next process: "if there is already a row with the same vendor_id and c_date, make an
UPDATE(to add the new hit count (NEW.c_num) to the saved one), but if it is missing the original INSERT will be
executed."
=# CREATE RULE r_logstat AS ON INSERT TO stat
WHERE EXISTS (SELECT * FROM stat WHERE vendor_id = new.vendor_id AND c_date= new.c_date)
DO INSTEAD UPDATE stat SET c_num = c_num + new.c_num WHERE vendor_id = new.vendor_id AND c_date= new.c_date;
4) and now, the query:
=> INSERT INTO stat (vendor_id, c_date, c_num)
SELECT vendor_id, current_date, count(*) FROM device WHERE [expressions select rows] GROUP BY vendor_id;
and the result (if there is a row that should be rather updated):
ERROR: ExecEvalAggref: no aggregates in this expression context
If you execute the previous query twice against an originally empty table stat, you get this error for the second
attempt.
This behaviour may be provided by triggers, too, but the reason why I'm forced to use the rule is:
(Progammer's Guide / chapter 8. Rules versus Triggers)
-"A trigger is fired for any row affected once. A rule manipulates the parsetree or generates an additional one. So if
manyrows are affected in one statement, a rule issuing one extra query would usually do a better job than a trigger
thatis called for any single row and must execute his operations this many times."
Is this problem in v7.1 or I must upgrade? (I'm using v7.0.2)
Thanks in advance,
- Papp Gyozo -
