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

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:26:18
Message-ID: e3ffbf241001120626s4b9e6d04qf6cab75d07b7cf1a@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2010-01-12 14:27:21 How to find column type
Previous Message Vincenzo Romano 2010-01-12 14:24:13 Re: Weird EXECUTE ... USING behaviour