From: a3a18850@telus.net
To: pgsql-sql@postgresql.org
Subject: Re: HELP: aggregating insert rule for multirow inserts.
Date: Thu, 5 May 2005 10:58:04 -0700
Message-ID: <1115315884.427a5eacd3ca9@webmail.telus.net> (raw)
In-Reply-To: <45caccdc050505003936efa435@mail.gmail.com>
References: <1115234855.4279222707401@webmail.telus.net>
<45caccdc050505003936efa435@mail.gmail.com>
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
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: a3a18850@telus.net
Subject: Re: HELP: aggregating insert rule for multirow inserts.
In-Reply-To: <1115315884.427a5eacd3ca9@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