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

[Sergey Levchenko]

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?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general