CODEWITHSUNDEEP
postgresqlmultidimensional-arrayplpgsql
Simon
Simon

Reputation: 9365

Value Assignment of multidimensional array fails in PL/PGSQL

I'm trying to materialize the content of a table into a multidimensional array in PL/PGSQL.

(Just for those who are wondering what the 'neighbors' are: The tables wnode and wedge define a graph with nodes wnode and edges wedge. Now I want to store the neighbors of a node v in an array contained in neighbors[v]).

This is what I've done so far:

DECLARE
    neighbors INTEGER[][];
    v INTEGER;
BEGIN
    FOR v IN SELECT n_id FROM wnode LOOP
        SELECT ARRAY ( SELECT n2_id FROM wedge WHERE n1_id=v) INTO neighbors[v];
    END LOOP;

    RETURN neighbors;

The preceding code fails due to a syntax error: 

ROW 7: ...( SELECT n2_id FROM wedge WHERE n1_id=v) INTO neighbors[v];
                                                                 ^

The next thing I tried was introducing a backing variable tmp:

DECLARE
    neighbors INTEGER[][];
    v INTEGER;
    tmp INTEGER[];
BEGIN
    FOR v IN SELECT n_id FROM wnode LOOP
        SELECT ARRAY ( SELECT n2_id FROM wedge WHERE n1_id=v) INTO tmp;
        neighbors[v] := tmp;
    END LOOP;

    RETURN neighbors;
END

I got this runtime error: 

ERROR:  invalid input syntax for integer: »{140,153,290,360,393}«

I also tried using SELECT array_append(neighbors, tmp) INTO neighbors; without any success.

I'm out of ideas right now. What I'd try next is iterate through tmp and assign each value neighbors[v][i] := tmp[i];... not really elegant.

I'd like to know how I can fill my multidimensional array in a more elegant way? (Maybe it is possible by using a single SQL-Query only?).

Thanks for your help. :-)

EDIT: Schema

Upvotes: 2

Views: 1712

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658747

This can probably be solved rather easily with the aggregate function outlined under this related question:
Selecting data into a Postgres array

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

Guess (since table definitions are missing):

SELECT INTO neighbors 
       array_agg_mult(ARRAY[e_arr]) AS mult_arr
FROM  (
   SELECT array_agg(n2_id) AS e_arr
   FROM   wedge
   GROUP  BY n1_id
   ORDER  BY n1_id
   ) e;

Update

You have to add an array-layer to make it a 2d-array.
Note that all 1D-arrays have to share the same length, or the query will error out.

->sqlfiddle to demonstrate both.

Upvotes: 1

Related Questions