DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=TZDB29F3bNhHoUXiVZvkmI/Y+UbRO66+xGusQfr1Y6OlSqUkAMeFW6s1GD/4v0z+bd
	H73EAYRhSBmwFPIFE+rUoMw0V+F4TA/A6vo40ItJLbDTEC67IAYyI+E/A8unRIbchzhO
	IZgNkDOVrBc88/NgJKr4bN30cQ4jQ6jpE8heg=
MIME-Version: 1.0
In-Reply-To: <003f01c9d7a6$4a6d9b90$df48d2b0$@es>
References: <003f01c9d7a6$4a6d9b90$df48d2b0$@es>
Date: Mon, 18 May 2009 15:31:57 +0200
Message-ID: <cc4f12900905180631x26fbca2fwc4be6776b1a5eceb@mail.gmail.com>
Subject: Re: SUM Array values query
From: Pawel Socha <pawel.socha@gmail.com>
To: =?ISO-8859-1?Q?Dani_Casta=F1os?= <dcastanos@androme.es>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

2009/5/18 Dani Casta=F1os <dcastanos@androme.es>:
> Hi all,
>
> I've this query including arrays:
>
> SELECT hour[1:5], statistics_date
> =A0FROM statistics_daily
> =A0WHERE statistics_date =3D to_date( '03/01/2008', 'DD/MM/YYYY' )
>
> Result:
>
> =A0 =A0 =A0 =A0 =A0 =A0hour =A0 =A0 =A0 =A0 =A0 =A0| statistics_date
> ----------------------------+-----------------
> =A0{1800,1800,1800,1800,1800} | 2008-01-03
>
>
> I'm just wondering if there's some way to retrieve the hour column as the
> sum of the array values... Just like this:
>
> =A0 =A0 =A0 =A0 =A0 =A0hour =A0 =A0 =A0 =A0 =A0 =A0| statistics_date
> ----------------------------+-----------------
> =A09000 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 2008-01-03
>
>
> Hour is a bigint[] array column.
> My version of postreSQL is 8.1.9
>
> Thank you in advanced!
>
> P.S.: Sorry if I had send it before, but I think I was not subscribed to =
the
> mailist.
>
> --
> Dani Casta=F1os S=E1nchez
> dcastanos@androme.es
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Create function in pure sql and ...

,-[15:27:18]merlin@[local]:5432=3D
`-merlin>create function array_sum (bigint[]) returns bigint as $$
SELECT sum($1[i])::bigint FROM
generate_series(array_lower($1,1),array_upper($1,1)
) index(i); $$ language sql;
CREATE FUNCTION
Time: 16,203 ms
,-[15:28:02]merlin@[local]:5432=3D
`-merlin>select array_sum(col_array) from t30;
 array_sum
-----------
      9000
(1 row)

:)

--=20
Pawel Socha
pawel.socha@gmail.com