From: Pavel Stehule <pavel.stehule@gmail.com>
To: Dani Castaños <dcastanos@androme.es>
Cc: pgsql-sql@postgresql.org
Subject: Re: Extracting data from arrays
Date: Mon, 18 May 2009 13:10:56 +0200
Message-ID: <162867790905180410n670062b0ud2d7fdd0e6521a2@mail.gmail.com> (raw)
In-Reply-To: <003b01c9d7a5$b7b22190$271664b0$@es>
References: <003b01c9d7a5$b7b22190$271664b0$@es>
Hello
postgres=# 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=# select sum_items(array[1,2,3,4]); sum_items
-----------
10
(1 row)
regards
Pavel Stehule
2009/5/18 Dani Castaños <dcastanos@androme.es>:
> Hi all,
>
> I've this query including arrays:
>
> SELECT hour[1:5], statistics_date
> FROM statistics_daily
> WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/YYYY' )
>
> Result:
>
> hour | statistics_date
> ----------------------------+-----------------
> {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:
>
> hour | statistics_date
> ----------------------------+-----------------
> 9000 | 2008-01-03
>
>
> Hour is a bigint[] array column.
> My version of postreSQL is 8.1.9
>
> Thank you in advanced!
>
> --
> Dani Castaños Sánchez
> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, dcastanos@androme.es
Subject: Re: Extracting data from arrays
In-Reply-To: <162867790905180410n670062b0ud2d7fdd0e6521a2@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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