Postgres的非关联二维数组 [英] dereferencing postgres 2d array

问题描述

所以，我注意到的Postgres（9.0）时不取消引用只有1维的二维数组喜欢它。这里有一个有趣的例子

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)

正如你可以看到这个回报率空当你不指定第二个维度。理想情况下，我想它返回内部数组 {10,11} 。因此，要解决这个问题我写了这个功能。

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?

推荐答案

考虑这个西方的全力施展你写它关闭之前：

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 有更多的例子

->sqlfiddle with more examples.

一些背景在这里和<一个手动 href=\"http://www.postgresql.org/docs/current/interactive/sql-ex$p$pssions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS\"相对=nofollow>这里。

这篇关于Postgres的非关联二维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！