Обсуждение: Looking for a way to sum integer arrays....

Поиск
Список
Период
Сортировка

Looking for a way to sum integer arrays....

От
Tony Wasson
Дата:
I'd like to be able to sum up an integer array. Like so:
     {3,2,1}  + {0,2,2}      -------     {3,4,3}

The following solution I've been hacking on works, although I think it
is far from "ideal". Is there a built in way to sum up arrays? If not,
is there a better way than my crude method? I have tested this on 7.4
and 8.0. I'd also be appreciate if any insight on why my aggregate
fails to work when I have an empty initcondition.  P.S. I have never
written an aggregate and I was lost trying to follow the complex_sum
example in the docs.
---------------------------------------------
CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS
INTEGER[] LANGUAGE 'plpgsql' AS '
/*
|| Author: Tony Wasson
||
|| Overview: Experiment with arrays and aggregates
||      3,2,1
||    + 0,2,2
||     -------
||      3,4,3
||
|| Revisions: (when, who, what)
||  2005/04/21 -- TW - Create function
*/
DECLARE   inta1   ALIAS FOR $1;   inta2   ALIAS FOR $2;   out_arr     INTEGER[];   out_arr_text    TEXT := '''';   i
      INTEGER;   nextnum     INTEGER; 
BEGIN   FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1)   LOOP       RAISE NOTICE ''looking at element %'',i;
 nextnum := COALESCE(inta1[i],0) + COALESCE(inta2[i],0);       RAISE NOTICE ''nextnum %'',nextnum;       out_arr_text
:=out_arr_text || nextnum::TEXT || '','';       RAISE NOTICE ''text %'',out_arr_text;   END LOOP;   RAISE NOTICE ''text
%'',out_arr_text;  --drop the last comma   IF SUBSTRING(out_arr_text,length(out_arr_text),1) =  '','' THEN
out_arr_text:= substring(out_arr_text,1,length(out_arr_text)-1);   END IF;   out_arr_text := ''{'' || out_arr_text ||
''}'';  RAISE NOTICE ''text %'',out_arr_text;   out_arr := out_arr_text;   RAISE NOTICE ''out_arr %'',out_arr; 
RETURN out_arr;
END
';

SELECT sum_intarray('{1,2}','{2,3}');
SELECT sum_intarray('{3,2,1}','{0,2,2}');

--- Now I make a table to demonstrate an aggregate on

CREATE TABLE arraytest (   id character varying(10) NOT NULL,   somearr integer[]
);

INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');


CREATE AGGREGATE sum_integer_array (   sfunc = sum_intarray,   basetype = INTEGER[],   stype = INTEGER[],   initcond =
'{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}'
);

----------------------

# SELECT sum_integer_array(somearr) FROM arraytest;                            sum_integer_array

---------------------------------------------------------------------------------{1,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

Thanks in advance to anyone who reads this far.

Tony Wasson
ajwasson@gmail.com

Re: Looking for a way to sum integer arrays....

От
"Sean Davis"
Дата:
You would definitely want to look into using pl/R for this.  Also, other 
procedure languages (perl, for example) work well with arrays so may be 
easier to use for this situation.  As for the aggregate, I don't know how to 
make that more dynamic in terms of return value.

Sean

----- Original Message ----- 
From: "Tony Wasson" <ajwasson@gmail.com>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, April 21, 2005 9:21 PM
Subject: [SQL] Looking for a way to sum integer arrays....


I'd like to be able to sum up an integer array. Like so:
     {3,2,1}  + {0,2,2}      -------     {3,4,3}

The following solution I've been hacking on works, although I think it
is far from "ideal". Is there a built in way to sum up arrays? If not,
is there a better way than my crude method? I have tested this on 7.4
and 8.0. I'd also be appreciate if any insight on why my aggregate
fails to work when I have an empty initcondition.  P.S. I have never
written an aggregate and I was lost trying to follow the complex_sum
example in the docs.
---------------------------------------------
CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS
INTEGER[] LANGUAGE 'plpgsql' AS '
/*
|| Author: Tony Wasson
||
|| Overview: Experiment with arrays and aggregates
||      3,2,1
||    + 0,2,2
||     -------
||      3,4,3
||
|| Revisions: (when, who, what)
||  2005/04/21 -- TW - Create function
*/
DECLARE   inta1   ALIAS FOR $1;   inta2   ALIAS FOR $2;   out_arr     INTEGER[];   out_arr_text    TEXT := '''';   i
      INTEGER;   nextnum     INTEGER;
 
BEGIN   FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1)   LOOP       RAISE NOTICE ''looking at element %'',i;
 nextnum := COALESCE(inta1[i],0) + COALESCE(inta2[i],0);       RAISE NOTICE ''nextnum %'',nextnum;       out_arr_text
:=out_arr_text || nextnum::TEXT || '','';       RAISE NOTICE ''text %'',out_arr_text;   END LOOP;   RAISE NOTICE ''text
%'',out_arr_text;  --drop the last comma   IF SUBSTRING(out_arr_text,length(out_arr_text),1) =  '','' THEN
out_arr_text:= substring(out_arr_text,1,length(out_arr_text)-1);   END IF;   out_arr_text := ''{'' || out_arr_text ||
''}'';  RAISE NOTICE ''text %'',out_arr_text;   out_arr := out_arr_text;   RAISE NOTICE ''out_arr %'',out_arr;
 
RETURN out_arr;
END
';

SELECT sum_intarray('{1,2}','{2,3}');
SELECT sum_intarray('{3,2,1}','{0,2,2}');

--- Now I make a table to demonstrate an aggregate on

CREATE TABLE arraytest (   id character varying(10) NOT NULL,   somearr integer[]
);

INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');


CREATE AGGREGATE sum_integer_array (   sfunc = sum_intarray,   basetype = INTEGER[],   stype = INTEGER[],   initcond =

'{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}'
);

----------------------

# SELECT sum_integer_array(somearr) FROM arraytest;                            sum_integer_array

---------------------------------------------------------------------------------{1,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}Thanks
inadvance to anyone who reads this far.Tony Wassonajwasson@gmail.com---------------------------(end of
broadcast)---------------------------TIP3: if posting/reading through Usenet, please send an appropriate
subscribe-nomailcommand to majordomo@postgresql.org so that your      message can get through to the mailing list
cleanly

Re: Looking for a way to sum integer arrays....

От
Tony Wasson
Дата:
Thank you for the responses!

To recap: pl/r array support works very well. In my case, I am looking
for pl/pgsql solution.

I also got this nice function from dennisb on the #postgresql irc
channel, which seems extremely "clean" and works with 7.4/8.0. My
original function didn't handle a blank initcond in the aggregate
gracefully.

CREATE OR REPLACE FUNCTION array_add(int[],int[]) RETURNS int[] AS ' DECLARE   x ALIAS FOR $1;   y ALIAS FOR $2;   a
int;  b int;   i int;   res int[]; BEGIN   res = x; 
   a := array_lower (y, 1);   b := array_upper (y, 1);
   IF a IS NOT NULL THEN     FOR i IN a .. b LOOP       res[i] := coalesce(res[i],0) + y[i];     END LOOP;   END IF;
   RETURN res; END;
'
LANGUAGE plpgsql STRICT IMMUTABLE;

--- then this aggregate lets me sum integer arrays...

CREATE AGGREGATE sum_integer_array (   sfunc = array_add,   basetype = INTEGER[],   stype = INTEGER[],   initcond =
'{}'
);


Here's how my sample table looked  and my new array summing aggregate
and function:

#SELECT * FROM arraytest ;id | somearr
----+---------a  | {1,2,3}b  | {0,1,2}
(2 rows)

#SELECT sum_integer_array(somearr) FROM arraytest ;sum_integer_array
-------------------{1,3,5}
(1 row)


Tony Wasson

Re: Looking for a way to sum integer arrays....

От
"Ramakrishnan Muralidharan"
Дата:
Hi,

CREATE OR REPLACE FUNCTION SUM_ARR( aArr1 Integer[] , aArr2 Integer[] )
RETURNS Integer[] AS $$
DECLARE aRetu Integer[];
BEGIN
 -- Initialize the Return array with first array value.
 FOR i IN array_lower( aArr1 )..array_upper( aArr1 ) LOOP     array_append( aRetu , aArr1[i] ); END LOOP;
 -- Add the second array value to return array
 FOR i IN array_lower( aArr2 )..array_upper( aArr2 ) LOOP     if i > array_upper( aRetu ) then array_append( aRetu ,
aArr2[i]);              else       aRetu[i] = aRetu[i]+aArr2[i];     end;  END LOOP; 
 RETURN aRetu;
END
$$ LANGUAGE 'plpgsql'

Regards,
R.Muralidharan


-----Original Message-----
From: Tony Wasson [mailto:ajwasson@gmail.com]
Sent: Friday, April 22, 2005 6:51 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Looking for a way to sum integer arrays....


I'd like to be able to sum up an integer array. Like so:
     {3,2,1}  + {0,2,2}      -------     {3,4,3}

The following solution I've been hacking on works, although I think it
is far from "ideal". Is there a built in way to sum up arrays? If not,
is there a better way than my crude method? I have tested this on 7.4
and 8.0. I'd also be appreciate if any insight on why my aggregate
fails to work when I have an empty initcondition.  P.S. I have never
written an aggregate and I was lost trying to follow the complex_sum
example in the docs.
---------------------------------------------
CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS
INTEGER[] LANGUAGE 'plpgsql' AS '
/*
|| Author: Tony Wasson
||
|| Overview: Experiment with arrays and aggregates
||      3,2,1
||    + 0,2,2
||     -------
||      3,4,3
||
|| Revisions: (when, who, what)
||  2005/04/21 -- TW - Create function
*/
DECLARE   inta1   ALIAS FOR $1;   inta2   ALIAS FOR $2;   out_arr     INTEGER[];   out_arr_text    TEXT := '''';   i
      INTEGER;   nextnum     INTEGER; 
BEGIN   FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1)   LOOP       RAISE NOTICE ''looking at element %'',i;
 nextnum := COALESCE(inta1[i],0) + COALESCE(inta2[i],0);       RAISE NOTICE ''nextnum %'',nextnum;       out_arr_text
:=out_arr_text || nextnum::TEXT || '','';       RAISE NOTICE ''text %'',out_arr_text;   END LOOP;   RAISE NOTICE ''text
%'',out_arr_text;  --drop the last comma   IF SUBSTRING(out_arr_text,length(out_arr_text),1) =  '','' THEN
out_arr_text:= substring(out_arr_text,1,length(out_arr_text)-1);   END IF;   out_arr_text := ''{'' || out_arr_text ||
''}'';  RAISE NOTICE ''text %'',out_arr_text;   out_arr := out_arr_text;   RAISE NOTICE ''out_arr %'',out_arr; 
RETURN out_arr;
END
';

SELECT sum_intarray('{1,2}','{2,3}');
SELECT sum_intarray('{3,2,1}','{0,2,2}');

--- Now I make a table to demonstrate an aggregate on

CREATE TABLE arraytest (   id character varying(10) NOT NULL,   somearr integer[]
);

INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');


CREATE AGGREGATE sum_integer_array (   sfunc = sum_intarray,   basetype = INTEGER[],   stype = INTEGER[],   initcond =
'{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}'
);

----------------------

# SELECT sum_integer_array(somearr) FROM arraytest;                            sum_integer_array

---------------------------------------------------------------------------------{1,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

Thanks in advance to anyone who reads this far.

Tony Wasson
ajwasson@gmail.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate     subscribe-nomail command to
majordomo@postgresql.orgso that your     message can get through to the mailing list cleanly