pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLooking for a way to sum integer arrays....
4+ messages / 3 participants
[nested] [flat]
* Looking for a way to sum integer arrays....
@ 2005-04-22 01:21 Tony Wasson <ajwasson@gmail.com>
2005-04-23 12:05 ` Re: Looking for a way to sum integer arrays.... Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 1 reply; 4+ messages in thread
From: Tony Wasson @ 2005-04-22 01:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Looking for a way to sum integer arrays....
2005-04-22 01:21 Looking for a way to sum integer arrays.... Tony Wasson <ajwasson@gmail.com>
@ 2005-04-23 12:05 ` Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Sean Davis @ 2005-04-23 12:05 UTC (permalink / raw)
To: Tony Wasson <ajwasson@gmail.com>; pgsql-sql
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 in advance to anyone who reads this far.Tony Wassonajwasson@gmail.com---------------------------(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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Looking for a way to sum integer arrays....
@ 2005-04-25 09:29 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2005-04-25 15:51 ` Re: Looking for a way to sum integer arrays.... Tony Wasson <ajwasson@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-04-25 09:29 UTC (permalink / raw)
To: Tony Wasson <ajwasson@gmail.com>; pgsql-sql
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.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Looking for a way to sum integer arrays....
2005-04-25 09:29 Re: Looking for a way to sum integer arrays.... Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
@ 2005-04-25 15:51 ` Tony Wasson <ajwasson@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tony Wasson @ 2005-04-25 15:51 UTC (permalink / raw)
To: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-04-25 15:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-22 01:21 Looking for a way to sum integer arrays.... Tony Wasson <ajwasson@gmail.com>
2005-04-23 12:05 ` Sean Davis <sdavis2@mail.nih.gov>
2005-04-25 09:29 Re: Looking for a way to sum integer arrays.... Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2005-04-25 15:51 ` Tony Wasson <ajwasson@gmail.com>
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