I've a table that looks like:
id | t
----+-------
1 | {1,2}
2 | {5,2}
3 | {6,2}
4 | {1,7}
5 | {1,8}
6 | {1,9}
I'm looking for a SELECT query that will return me array of t like {{1,2}, {5,2}, ... {1,9}}.
Had t not been an array data type, it would have been easy like:
SELECT ARRAY (SELECT t from tbl_foo);
Can same be done if data type is int[]?
I don't know if there is some simpler way (I hope so), but this works (PostgreSQL doesn't have array of arrays, so array_agg aproach doesn't work here):
CREATE OR REPLACE FUNCTION func()
RETURNS int[] AS $$
DECLARE
arr int[];
res int[];
n int;
BEGIN
res := '{{0, 0}}';
FOR arr IN SELECT t FROM tbl_foo
LOOP
res := res || arr;
END LOOP;
n := array_length(res, 1);
RETURN res[2:n];
END $$
LANGUAGE 'plpgsql';
Example:
CREATE TABLE tbl_foo (id serial, t int[]);
INSERT INTO tbl_foo (t) VALUES
('{1, 2}'),
('{5, 2}'),
('{6, 2}'),
('{1, 7}'),
('{1, 8}'),
('{1, 9}');
SELECT func();
func
---------------------------------------
{{1,2},{5,2},{6,2},{1,7},{1,8},{1,9}}
(1 row)
EDIT:
Second solution is based on new aggregate function, called let's say array2_agg:
CREATE OR REPLACE FUNCTION array2_agg_cutFirst(res anyarray)
RETURNS anyarray AS $$
BEGIN
RETURN res[2:array_length(res, 1)];
END $$
LANGUAGE 'plpgsql';
CREATE AGGREGATE array2_agg(anyarray)
(
SFUNC = array_cat,
STYPE = anyarray,
FINALFUNC = array2_agg_cutFirst,
INITCOND = '{{0, 0}}'
);
SELECT array2_agg(t) FROM tbl_foo;
array2_agg
---------------------------------------
{{1,2},{5,2},{6,2},{1,7},{1,8},{1,9}}
(1 row)
I need that array2_agg_cutFirst function (simply cutting first '{0, 0}' subarray), because INITCOND = '{{}}' is not allowable.
