Is there any easy way of converting a postgres table in to an 2 dimensional?
I have a data table foobar with two columns foo and bar which has the following data in 1,2 3,4 5,6
I want to convert it in
{
{1,2},
{3,4},
{5,6}
}
I have tried things like
select ARRAY[foo,bar] from foobar
which creates
{1,2}
{3,4}
{5,6}
Which is nearly there
I suspect that I am going to have to write pgpsql function to do this? Any suggestions?
Here's what I did for LedgerSMB:
CREATE AGGREGATE compound_array (
BASETYPE = ANYARRAY,
STYPE = ANYARRAY,
SFUNC = ARRAY_CAT,
INITCOND = '{}'
);
Then you can:
select compound_array(ARRAY[[foo,bar]]) from foobar;
Note you need to have two pairs of square brackets because otherwise it just adds them in a one dimensional array.
create or replace function my_array()
returns integer[] as $function$
declare
r record;
a integer[];
begin
for r in
select foo, bar
from (values
(1, 2), (3, 4), (5, 6)
) foobar (foo, bar)
loop
a := a || array[[r.foo, r.bar]];
end loop;
return a;
end;
$function$ language plpgsql;
select my_array();
my_array
---------------------
{{1,2},{3,4},{5,6}}
select (my_array())[2][2];
my_array
----------
4
select array_agg(ARRAY[foo,bar]) from foobar
