current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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

share|improve this question
    
I added second, custom aggregate based solution, which I think is better than just pure function. –  Grzegorz Szpetkowski Jul 10 '11 at 21:55

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Thank Grzegorz Szpetkowski. You are awesome :) –  Mayank Jul 11 '11 at 9:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.