Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 7C5F5634AA0
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 18 May 2009 08:10:58 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 84521-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 18 May 2009 08:10:57 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.30])
	by mail.postgresql.org (Postfix) with ESMTP id 4C65D634371
	for <pgsql-sql@postgresql.org>; Mon, 18 May 2009 08:10:57 -0300 (ADT)
Received: by yw-out-2324.google.com with SMTP id 3so1796912ywj.73
	for <pgsql-sql@postgresql.org>; Mon, 18 May 2009 04:10:56 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=BaG0HzCMQavfsrYnWy/qxjrOvMWMzdftEdMvk57sJhY=;
	b=llU7JJDMIgpsnAhDsI6qRuj3YUcGABKDnJY+8kvix3WKk8aL8zWfiBAkeOMltFfpmn
	4NXhOxxlmidBwOvIsmmHfLwDfZWmb0bUAYDPQ2Ew6PuEtyKiG6MAqSIkMc0LPhncZtX+
	MLCu+3gU/PTGx9t/l+57s9i77VpmphKL4WDJ0=
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=Lwo/suebFhG0YFc3EZBayikhwcg89xR9lf7J3Wej7RDmB51yw3vK+xISxnTKnXgvBQ
	81NE4Z6DZt4VFdCijr8zn/gZbAg6gWCcl7wjaIuuOI7ar+buMtLhoslfdSwyZ/ZEI2yj
	wWW/wUif/5my2KlqOIJCn3l7X8KiiBiLToymI=
MIME-Version: 1.0
Received: by 10.151.132.6 with SMTP id j6mr12296974ybn.96.1242645056129; Mon,
	18 May 2009 04:10:56 -0700 (PDT)
In-Reply-To: <003b01c9d7a5$b7b22190$271664b0$@es>
References: <003b01c9d7a5$b7b22190$271664b0$@es>
Date: Mon, 18 May 2009 13:10:56 +0200
Message-ID: <162867790905180410n670062b0ud2d7fdd0e6521a2@mail.gmail.com>
Subject: Re: Extracting data from arrays
From: Pavel Stehule <pavel.stehule@gmail.com>
To: =?UTF-8?Q?Dani_Casta=C3=B1os?= <dcastanos@androme.es>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200905/73
X-Sequence-Number: 32565

Hello

postgres=3D# create or replace function sum_items(bigint[]) returns
bigint as $$ select sum($1[i])::bigint from
generate_series(array_lower($1,1), array_upper($1,1)) g(i)$$ language
sql immutable;
CREATE FUNCTION
Time: 2,510 ms
postgres=3D# select sum_items(array[1,2,3,4]); sum_items
-----------
        10
(1 row)

regards
Pavel Stehule


2009/5/18 Dani Casta=C3=B1os <dcastanos@androme.es>:
> Hi all,
>
> I've this query including arrays:
>
> SELECT hour[1:5], statistics_date
> =C2=A0FROM statistics_daily
> =C2=A0WHERE statistics_date =3D to_date( '03/01/2008', 'DD/MM/YYYY' )
>
> Result:
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0hour =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0| statistics_date
> ----------------------------+-----------------
> =C2=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:
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0hour =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0| statistics_date
> ----------------------------+-----------------
> =C2=A09000 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 | 2008-01-03
>
>
> Hour is a bigint[] array column.
> My version of postreSQL is 8.1.9
>
> Thank you in advanced!
>
> --
> Dani Casta=C3=B1os S=C3=A1nchez
> 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
>