Postgresql: select an array

Question

I have a table that looks like this:

id | t ----+------- 1 | {1,2} 2 | {5,2} 3 | {6,2} 4 | {1,7} 5 | {1,8} 6 | {1,9} 

I am looking for a SELECT query that will return an array t to me, for example {{1,2}, {5,2}, ... {1,9}} .

If t not an array data type, it would be easy:

 SELECT ARRAY (SELECT t from tbl_foo); 

Can this be done if the data type is int[] ?

Answer 1

I do not know if there is an easier way (I hope so), but it works (PostgreSQL does not have an array of arrays, so array_agg aproach does not 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:

The second solution is based on a new aggregate function called let 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 the function array2_agg_cutFirst (just shortening the first subframe '{0, 0}' ) because INITCOND = '{{}}' not valid.