From: | Sergey Levchenko <sector119(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: need help with query, how to fold select result to array? |
Date: | 2010-01-12 14:06:20 |
Message-ID: | e3ffbf241001120606t1d2e1000ua9fa192572a0bbb1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
but I have to fold it in multi-dimensional, array like {{71629130,
15518, 0}, {2668722, 616, 0}} ?
but it's not possible to pass more then one argument to the array_agg
function :/
2010/1/12 A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>:
> In response to Sergey Levchenko :
>> eps=# SELECT office_id, serial, commit_date, service_id, meter_id,
>> organization_reading, reading FROM meter_readings WHERE office_id =
>> 134 AND serial = 27 AND commit_date = '2010-01-11' AND commit_time =
>> '13:44:37' AND person_id = 300871;
>>
>> office_id | serial | commit_date | service_id | meter_id |
>> organization_reading | reading
>> -----------+--------+-------------+------------+----------+----------------------+---------
>> 134 | 27 | 2010-01-11 | 2 | 71629130 |
>> 15518 | 0
>> 134 | 27 | 2010-01-11 | 2 | 2668722 |
>> 616 | 0
>> 134 | 27 | 2010-01-11 | 75 | 111029 |
>> 9505 | 0
>> 134 | 27 | 2010-01-11 | 4 | 019210 |
>> 372 | 0
>> (4 rows)
>>
>> How to get ?
>> 134 | 27 | 2010-01-11 | 2 | {{71629130, 15518,
>> 0}, {2668722, 616, 0}}
>> 134 | 27 | 2010-01-11 | 75 | {111029, 9505, 0}
>> 134 | 27 | 2010-01-11 | 4 | {019210, 372, 0}
>>
>> Thanks a lot!
>
> You can use array_agg(since 8.4):
>
> test=# create table bla (id int, value text);
> CREATE TABLE
> test=*# copy bla from stdin;
> Enter data to be copied followed by a newline.
> End with a backslash and a period on a line by itself.
>>> 1 bla
>>> 1 foo
>>> 1 bar
>>> 2 foobar
>>> 2 test
>>> \.
> test=*# select id, array_agg(value) from bla group by 1 order by 1;
> id | array_agg
> ----+---------------
> 1 | {bla,foo,bar}
> 2 | {foobar,test}
> (2 rows)
>
>
> If you don't have 8.4 search the docu for array_accum:
> http://www.postgresql.org/docs/8.4/interactive/xaggr.html
>
>
> Regards, Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Vincenzo Romano | 2010-01-12 14:09:04 | Weird EXECUTE ... USING behaviour |
Previous Message | A. Kretschmer | 2010-01-12 13:55:52 | Re: need help with query, how to fold select result to array? |