DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=TEi5WSwMYExnWUOnMoUBlkYqM3riKoY0B5NTQdBaru0fBsTrBnJF+komH+UU+Y0rJLSqHUcd9lWned3dP28TiIiK/o/W82lkprx+e2eNZPAPXK6JT45ne6Cfhl3VX52W3CW9Zw1Kyu6IeDzvJm+oljRktgNg9etlkWM2wOprnBA=
Message-ID: <45caccdc050505003936efa435@mail.gmail.com>
Date: Thu, 5 May 2005 00:39:18 -0700
From: Mikey <mikeboscia@gmail.com>
Reply-To: Mikey <mikeboscia@gmail.com>
To: Mischa Sandberg <mischa.sandberg@telus.net>
Subject: Re: HELP: aggregating insert rule for multirow inserts.
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1115234855.4279222707401@webmail.telus.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <1115234855.4279222707401@webmail.telus.net>

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 =3D (
               select count(*)
               from critter
               where critter.legs =3D zoostats.legs
       ) where zoostats.legs =3D new.legs
);

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
OUTPUT (from entire script)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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
=3D=3D=3D=3D=3D=3D=3D=3D=3D
End OUTPUT
=3D=3D=3D=3D=3D=3D=3D=3D=3D

(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.
>=20
> 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'.
>=20
> I've tried different flavours of the UPDATE command,
> in the following rule, and they either produce syntax errors
> or the wrong results.
>=20
> Any suggestions much appreciated ...
>=20
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CODE
> "How many critters are in the zoo, of the 4,5,6...-legged varieties?"
>=20
> create table critter(name text, legs int);
> create table zoostats(legs int, headcount int default 0,
>                       primary key(legs));
>=20
> create or replace rule critter_counter as
> on INSERT to critter do (
>=20
>     insert into zoostats
>         select distinct new.legs
>         where new.legs not in (select legs from zoostats);
>=20
>     update zoostats
>     set    headcount =3D headcount + (select count(*)) -- "from new"
>     where new.legs =3D zoostats.legs
> );
>=20
> insert into critter values('cat',4);
> insert into critter values('starfish',5);
> insert into critter values('ant',6);
> insert into critter values('dog',4);
>=20
> insert into critter select * from critter; -- double everything.
>=20
> select * from zoostats;
>=20
> drop table zoostats cascade;
> drop table critter;
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D EXPECT=
ED OUTPUT
> legs headcount
> ---- ---------
>    4         4
>    5         2
>    6         2
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D ACTUAL=
 OUTPUT
> legs headcount
> ---- ---------
>    4         3      -- !?
>    5         2
>    6         2
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D OTHER =
ATTEMPT:
> This version of the update looks syntactically right to me,
> but makes CREATE RULE fail on a syntax error:
>=20
>  ...
>=20
> update zoostats
> set headcount =3D headcount + tally
> from (select new.legs, count(new.legs) as tally -- from new !?
>         group by new.legs) as poll
> where poll.legs =3D zoostats.legs;
>=20
> 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.
>=20
> ---------------------------(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
>=20


--=20
Time is my Nemesis!
But, if I stopped time for one year,=20
how would I know when that year was finished?...