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?
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 |