insert-select once more

From: "Gyozo Papp" <pgerzson(at)freestart(dot)hu>
To: <pgsql-general(at)postgresql(dot)org>
Subject: insert-select once more
Date: 2001-04-16 12:22:42
Message-ID: 001501c0c670$1023eac0$e54ac5d5@jaguar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 of search 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 many rows are affected in one statement, a rule issuing one extra query would usually do a better job than a trigger that is 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 -

Attachment Content-Type Size
explain-insert-select.txt text/plain 8.8 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Justin Clift 2001-04-16 13:14:05 Re: Success: PostgreSQL 7.1 on MacOS X (Darwin 1.3)
Previous Message Riebs, Andy 2001-04-16 12:19:14 RE: Benchmarking PostgreSQL