pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExtracting data from arrays
4+ messages / 3 participants
[nested] [flat]
* Extracting data from arrays
@ 2009-05-18 10:45 Dani Castaños <dcastanos@androme.es>
0 siblings, 1 reply; 4+ messages in thread
From: Dani Castaños @ 2009-05-18 10:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Extracting data from arrays
@ 2009-05-18 11:10 Pavel Stehule <pavel.stehule@gmail.com>
parent: Dani Castaños <dcastanos@androme.es>
0 siblings, 2 replies; 4+ messages in thread
From: Pavel Stehule @ 2009-05-18 11:10 UTC (permalink / raw)
To: Dani Castaños <dcastanos@androme.es>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Extracting data from arrays
@ 2009-05-18 12:16 Harald Fuchs <hari.fuchs@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Harald Fuchs @ 2009-05-18 12:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Extracting data from arrays
@ 2009-05-18 13:25 Dani Castaños <dcastanos@androme.es>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Dani Castaños @ 2009-05-18 13:25 UTC (permalink / raw)
To: 'Pavel Stehule' <pavel.stehule@gmail.com>; +Cc: pgsql-sql
Thank you very much!
-----Mensaje original-----
De: Pavel Stehule [mailto:pavel.stehule@gmail.com]
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-05-18 13:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-05-18 10:45 Extracting data from arrays Dani Castaños <dcastanos@androme.es>
2009-05-18 11:10 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-05-18 12:16 ` Harald Fuchs <hari.fuchs@gmail.com>
2009-05-18 13:25 ` Dani Castaños <dcastanos@androme.es>
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