To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: Extracting data from arrays
Date: Mon, 18 May 2009 14:16:35 +0200
Lines: 55
Message-ID: <puzlda7gik.fsf@srv.protecting.net>
References: <003b01c9d7a5$b7b22190$271664b0$@es>
	<162867790905180410n670062b0ud2d7fdd0e6521a2@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-15
Content-Transfer-Encoding: 8bit
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
Cancel-Lock: sha1:HcJRubm58miFwroHd6tP9OmdCfw=

In article <162867790905180410n670062b0ud2d7fdd0e6521a2@mail.gmail.com>,
Pavel Stehule <pavel.stehule@gmail.com> writes:

> 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.

FWIW, in PostgreSQL 8.4 you won't need your own sum_items function any
more:

SELECT sum(unnest), statistics_date
FROM (
    SELECT statistics_date, unnest(hour)
    FROM t1
  ) AS dummy
WHERE statistics_date = '2008-01-03'
GROUP BY statistics_date