HELP: aggregating insert rule for multirow inserts.
От | Mischa Sandberg |
---|---|
Тема | HELP: aggregating insert rule for multirow inserts. |
Дата | |
Msg-id | 1115234855.4279222707401@webmail.telus.net обсуждение исходный текст |
Ответы |
Re: HELP: aggregating insert rule for multirow inserts.
|
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: