Message-ID: <1115234855.4279222707401@webmail.telus.net>
Date: Wed,  4 May 2005 12:27:35 -0700
From: Mischa Sandberg <mischa.sandberg@telus.net>
To: pgsql-sql@postgresql.org
Subject: HELP: aggregating insert rule for multirow inserts.
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.1-cvs

I'm having a problem with the use of the NEW rowset,
in a rule intended to aggregate across inserts. 

I've never really grokked how NEW and OLD really work, 
syntactically, other than that they seem to be implicit 
in every top-level FROM clause, and any mention elsewhere
gets an error: '42P01: relation "*NEW*" does not exist'.

I've tried different flavours of the UPDATE command,
in the following rule, and they either produce syntax errors
or the wrong results.

Any suggestions much appreciated ...

====================== CODE
"How many critters are in the zoo, of the 4,5,6...-legged varieties?"

create table critter(name text, legs int);
create table zoostats(legs int, headcount int default 0,
                      primary key(legs));

create or replace rule critter_counter as
on INSERT to critter do (

    insert into zoostats
        select distinct new.legs
        where new.legs not in (select legs from zoostats);

    update zoostats
    set    headcount = headcount + (select count(*)) -- "from new"
    where new.legs = zoostats.legs
);

insert into critter values('cat',4);
insert into critter values('starfish',5);
insert into critter values('ant',6);
insert into critter values('dog',4);

insert into critter select * from critter; -- double everything.

select * from zoostats;

drop table zoostats cascade;
drop table critter;
====================== EXPECTED OUTPUT
legs headcount
---- ---------
   4         4
   5         2
   6         2
====================== ACTUAL OUTPUT
legs headcount
---- ---------
   4         3      -- !?
   5         2
   6         2
====================== OTHER ATTEMPT:
This version of the update looks syntactically right to me,
but makes CREATE RULE fail on a syntax error:

 ...

update zoostats
set headcount = headcount + tally
from (select new.legs, count(new.legs) as tally -- from new !?
        group by new.legs) as poll
where poll.legs = zoostats.legs;

ERROR: 'Subquery in FROM may not refer to other relations
        of same query level'.
-- 
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.