Re: insert-select once more

On Mon, Apr 16, 2001 at 02:22:42PM +0200, Gyozo Papp wrote:
> 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;

that's a neat concept. in my mind <guess> the WHERE allows you to
position 'cursors' within various tables to get certain values
from them, before the rule takes hold.

--DROP RULE prof_insert;
CREATE RULE prof_insert AS
ON INSERT TO prof
WHERE
_faculty.who = get_whoid( NEW.login )
AND
_faculty.edu = get_eduid( NEW.educode )
DO INSTEAD
INSERT INTO _prof (
course,
who,
-- status,
editor
) VALUES (
get_courseid( NEW.educode, NEW.topiccode, NEW.coursecode, NEW.language, NEW.medium ),
_faculty.who, -- <<< specified via above WHERE clause
-- NEW.status,
NEW.editor
)
;

what that does, is get a _faculty.who pointer based on NEW.login
and NEW.educode; it does NOT tell postgres "if there is none,
then ignore this rule".

</guess> i think.

> 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.

which meshes with my theory. but i still could be wrong. :)

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!