Re: need help with query, how to fold select result to array?

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!

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

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
>

CREATE TYPE varchararray AS (f varchar[]);

SELECT office_id, serial, commit_date, service_id,
array_agg(DISTINCT ROW(ARRAY(SELECT meter_id::varchar UNION ALL

SELECT organization_reading::varchar UNION ALL

SELECT reading::varchar))::varchararray)
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
GROUP BY office_id, serial, commit_date, service_id;

office_id | serial | commit_date | service_id |
array_agg
-----------+--------+-------------+------------+------------------------------------------------------
134 | 27 | 2010-01-11 | 2 |
{"(\"{2668722,616,0}\")","(\"{71629130,15518,0}\")"}
134 | 27 | 2010-01-11 | 4 | {"(\"{019210,372,0}\")"}
134 | 27 | 2010-01-11 | 75 | {"(\"{111029,9505,0}\")"}
(3 rows)

I want something like that, but without varchararray type;
parentheses, slashes and " in array_agg field, where does it get from?

On Tue, Jan 12, 2010 at 04:06:20PM +0200, Sergey Levchenko wrote:
> 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 :/

Tuples work fine. Multidimensional arrays are awfully complicated and
it's generally better to stay away from them, arrays of tuples make more
sense. Maybe something like:

CREATE TYPE meterreading (
meter_id INT,
org_reading NUMERIC,
reading NUMERIC
);
SELECT office_id, serial, commit_date, service_id,
array_agg(row(meter_id, organization_reading, reading)::meterreading) AS readings
FROM meter_readings;

--
Sam http://samason.me.uk/