From: Mischa Sandberg <mischa.sandberg@telus.net>
To: pgsql-sql@postgresql.org
Subject: HELP: aggregating insert rule for multirow inserts.
Date: Wed, 4 May 2005 12:27:35 -0700
Message-ID: <1115234855.4279222707401@webmail.telus.net> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mischa.sandberg@telus.net
Subject: Re: HELP: aggregating insert rule for multirow inserts.
In-Reply-To: <1115234855.4279222707401@webmail.telus.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox