dereferencing postgres 2d array

Question

So I noticed that postgres (9.0) doesn't like it when you dereference a 2d array with only 1 dimension. Here's a funny example

WITH my_table(arr) AS ( VALUES (ARRAY[[10,11],[20,21]]) )
SELECT arr[2][1] AS good, arr[1] AS bad FROM my_table;

 good | bad
------+-----
   20 |
(1 row)

As you can see this returns null when you don't specify the second dimension. Ideally I'd like it to return the inner array {10,11}. So to solve this I wrote this function

CREATE OR REPLACE FUNCTION deref_2d(orig_arr numeric[][], inner_arr int)
RETURNS numeric[] AS $$
DECLARE
  index int;
  len int;
  return_arr numeric[];
BEGIN
  len := array_upper(orig_arr,2);
  FOR index IN 1 .. len LOOP
    return_arr[index] := orig_arr[inner_arr][index];
  END LOOP;
  RETURN return_arr;
END
$$ LANGUAGE plpgsql;

And now I can write:

WITH my_table(arr) AS ( VALUES (ARRAY[[10,11],[20,21]]) )
SELECT deref_2d(arr,1) FROM my_table;
 deref_2d
----------
 {10,11}

But multiple things about this make me feel uneasy about my solution. Is there a better way to do this?

Erwin Brandstetter · Accepted Answer · 2013-02-07 22:04:08Z

3

Consider the full cast of this Western before you write it off:

WITH tbl(arr) AS (SELECT (ARRAY[[10,11],[20,21]]))
SELECT arr[2][1]    AS the_good
      ,arr[1]       AS the_bad
      ,arr[1:1]     AS the_ugly   -- first slice of 2D-array
      ,arr[1][1:2]  AS the_ugly_twin -- 2D-array with elements 1 & 2 of 1st slice
      ,ARRAY((SELECT unnest(arr[1:1]))) AS the_righteous -- 1D-array of 1st slice
FROM   tbl;

->sqlfiddle with more examples.

Some background in the manual here and here.

edited Feb 7, 2013 at 22:04

answered Feb 7, 2013 at 4:07

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wesdotcool Over a year ago

Array slicing FTW! Thanks for showing me this syntax. Where is this documented? I've only been able to find small examples (like this) of most of the postgres nuances.

Erwin Brandstetter Over a year ago

@wesdotcool: I have learned a few tricks in the last 12 years I have been working with Postgres. Added links to the manual.

Collectives™ on Stack Overflow

dereferencing postgres 2d array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related