From: | Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Extracting data from arrays |
Date: | 2009-05-18 12:16:35 |
Message-ID: | puzlda7gik.fsf@srv.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
In article <162867790905180410n670062b0ud2d7fdd0e6521a2(at)mail(dot)gmail(dot)com>,
Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(at)androme(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Gerardo Herzig | 2009-05-18 12:29:25 | proposal for a CookBook in postgresql.org |
Previous Message | Dave Page | 2009-05-18 11:57:32 | Re: proposal for a CookBook in postgresql.org |