Re: [SQL] HELP: aggregating insert rule for multirow inserts.

[Mikey]

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 <[EMAIL PROTECTED]> 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
> setheadcount = 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 [EMAIL PROTECTED] 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?...

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] multi-column unique constraints with nullable columns

[Mikey]

It may be possible to bypass the NULL != NULL by using coalesce.  Here
is an example:



create table foo2 (
a integer not null,
b integer not null,
c integer null,
UNIQUE (a,b,c)
);

create function foo2_unique_func() RETURNS trigger AS '
DECLARE
isfound integer = 0;
BEGIN
   isfound = (select count(*) from foo2 where
(new.a,new.b,coalesce(new.c::TEXT,''EmPtY'')) in (select
a,b,coalesce(c::TEXT,''EmPtY'')
from foo2));
RAISE NOTICE ''isfound: %'', isfound;
   IF isfound > 0  THEN
   RAISE EXCEPTION ''Columns a,b,c Must Be Unique values
(%,%,%)'', new.a, new.b, new.c;
   ELSE
   RETURN NEW;
   END IF;
END; '  language 'plpgsql';

CREATE TRIGGER foo2_unique BEFORE INSERT OR UPDATE ON foo2
   FOR EACH ROW EXECUTE PROCEDURE foo2_unique_func();


insert into foo2 values (1,300, null);
insert into foo2 values (1,300, null);

select * from foo2;
select * from foo2 where (1,300,coalesce(null::TEXT,'EmPtY')) in
(select a,b,coalesce(c::TEXT,'EmPtY') from foo2);

drop table foo2 cascade;
drop function foo2_unique_func() cascade;



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]