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@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!