CODEWITHSUNDEEP
postgresqlmultidimensional-array
Dave
Dave

Reputation: 585

postgres rows to 2 dimensional array

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?

Upvotes: 2

Views: 5783

Answers (3)

girgen
girgen

Reputation: 119

select array_agg(ARRAY[foo,bar]) from foobar

Upvotes: 1

Chris Travers
Chris Travers

Reputation: 26454

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.

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

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

Upvotes: 1

Related Questions