Thread: HELP: aggregating insert rule for multirow inserts.
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.
What about this as a rule. It seems to work for me. 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 = ( select count(*) from critter where critter.legs= zoostats.legs ) where zoostats.legs = new.legs ); =============== OUTPUT (from entire script) =============== CREATE TABLE psql:pglist-problem.1.sql:4: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'zoostats_pkey' for table 'zoostats' CREATE TABLE CREATE RULE INSERT 17347 1 INSERT 17349 1 INSERT 17351 1 INSERT 17353 1 name | legs ----------+------ cat | 4 starfish | 5 ant | 6 dog | 4 (4 rows) legs | headcount ------+----------- 5 | 1 6 | 1 4 | 2 (3 rows) INSERT 0 4 legs | headcount ------+----------- 4 | 4 5 | 2 6 | 2 (3 rows) psql:pglist-problem.1.sql:32: NOTICE: Drop cascades to rule critter_counter on table critter DROP TABLE DROP TABLE ========= End OUTPUT ========= (oops forgot to send to list) --Mike On 5/4/05, Mischa Sandberg <mischa.sandberg@telus.net> wrote: > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Time is my Nemesis! But, if I stopped time for one year, how would I know when that year was finished?...
Quoting Mikey <mikeboscia@gmail.com>: > What about this as a rule. It seems to work for me. > > 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 = ( > select count(*) > from critter > where critter.legs = zoostats.legs > ) where zoostats.legs = new.legs > ); ... Thanks, sorry I didn't mention, I was looking for an efficient solution, given that (I thought) using a RULE that processes the whole (in-memory) NEW set in one operation is faster than a trigger that increments each headcount for each inserted row. It comes from the way of thinking when you work with Sybase/MSSQL triggers, and their INSERTED and DELETED pseudotables --- what you would get in FOR-EACH-STATEMENT triggers that implement NEW_TABLE AS and OLD_TABLE AS. Yes, recalculating each affected leg-count group from scratch will work. But in the above, adding one 4legged animal requires rescan of a large number of animals ... Hope the zoo metaphor isn't getting a bit tedious, folks. > On 5/4/05, Mischa Sandberg <mischa.sandberg@telus.net> wrote: > > 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