pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHELP: aggregating insert rule for multirow inserts.
3+ messages / 3 participants
[nested] [flat]
* HELP: aggregating insert rule for multirow inserts.
@ 2005-05-04 19:27 Mischa Sandberg <mischa.sandberg@telus.net>
2005-05-05 07:39 ` Re: HELP: aggregating insert rule for multirow inserts. Mikey <mikeboscia@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mischa Sandberg @ 2005-05-04 19:27 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: HELP: aggregating insert rule for multirow inserts.
2005-05-04 19:27 HELP: aggregating insert rule for multirow inserts. Mischa Sandberg <mischa.sandberg@telus.net>
@ 2005-05-05 07:39 ` Mikey <mikeboscia@gmail.com>
2005-05-05 17:58 ` Re: HELP: aggregating insert rule for multirow inserts. a3a18850@telus.net
0 siblings, 1 reply; 3+ messages in thread
From: Mikey @ 2005-05-05 07:39 UTC (permalink / raw)
To: Mischa Sandberg <mischa.sandberg@telus.net>; +Cc: pgsql-sql
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?...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: HELP: aggregating insert rule for multirow inserts.
2005-05-04 19:27 HELP: aggregating insert rule for multirow inserts. Mischa Sandberg <mischa.sandberg@telus.net>
2005-05-05 07:39 ` Re: HELP: aggregating insert rule for multirow inserts. Mikey <mikeboscia@gmail.com>
@ 2005-05-05 17:58 ` a3a18850@telus.net
0 siblings, 0 replies; 3+ messages in thread
From: a3a18850@telus.net @ 2005-05-05 17:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-05-05 17:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-04 19:27 HELP: aggregating insert rule for multirow inserts. Mischa Sandberg <mischa.sandberg@telus.net>
2005-05-05 07:39 ` Mikey <mikeboscia@gmail.com>
2005-05-05 17:58 ` a3a18850@telus.net
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