arrays - plpgsql 函数在数组中搜索和移动索引

Question

我正在尝试编写一个函数来查找二维数组中给定元素的给定位置前面的元素。基本上它涉及为给定元素查找数组中的所有位置并将位置向后移动几步的步骤。

我创建了一个函数来返回元素的所有匹配项。但是需要修改函数才能将索引从给定元素的位置向后移动几个位置。

查看下面的详细信息:

dev=# \d+ test
                          Table "public.test"
 Column  |  Type   | Modifiers | Storage  | Stats target | Description
---------+---------+-----------+----------+--------------+-------------
 id      | integer |           | plain    |              |
 actions | text[]  |           | extended |              |

dev=# select * from test;
 id |                                                actions
----+-------------------------------------------------------------------------------------------------------
  1 | {{a,1,1,1},{b,3,2,3},{c,2,3,4},{d,5,6,7},{e,3,4,5},{f,6,7,8},{g,0,9,0},{a,2,3,4},{k,3,4,5},{a,2,3,7}}
(1 row)

--function to get element of current position
CREATE OR REPLACE FUNCTION find_in_array(needle ANYELEMENT, haystack ANYARRAY,OUT cname text,out val1 numeric,out val2 numeric,out val3 numeric)
RETURNS setof record
  AS $$   
SELECT $2[i][1],$2[i][2]::numeric,$2[i][3]::numeric,$2[i][4]::numeric
    FROM generate_series(array_lower($2,1), array_upper($2,1)) AS i
     WHERE $2[i][1] = $1;   
$$ LANGUAGE sql STABLE;


dev=# select find_in_array('a',actions) from test;
 find_in_array
---------------
 (a,1,1,1)
 (a,2,3,4)
 (a,2,3,7)
(3 rows)

现在，给定元素 a，我想找到字母 'a' 前面大约 2-3 个位置的元素(如果找到具有相同字母的元素，则跳过它)。在示例中，它将是:(e,3,4,5)(女,6,7,8)(a,2,3,4) --跳过，因为它有相同的字母a。(g,0,9,0)

根据@Ziggy的回答，我修改了上面的函数，让它后移了几个位置。进一步从当前代码来看，如果在数组的第一个元素处有一个匹配项，我希望返回像 'direct',0,0,0 这样的行

CREATE OR REPLACE FUNCTION find_in_array_move(needle ANYELEMENT, haystack ANYARRAY)
  RETURNS TABLE (cname text, val1 numeric, val2 numeric, val3 numeric)
  AS $$
    SELECT case when i=1 then 'direct' else $2[i-i_move][1] end,
    case when i=1 then 0 else $2[i-i_move][2]::numeric end,
    case when i=1 then 0 else $2[i-i_move][3]::numeric end,
    case when i=1 then 0 else $2[i-i_move][4]::numeric end
    FROM generate_series(2, 3) i_move,
         generate_series(array_lower($2,1), array_upper($2,1)) AS i
    WHERE $2[i][1] = $1
$$ LANGUAGE SQL STABLE;

结果是:

 dev=# select find_in_array_move('a',actions) from test;
 find_in_array_move
--------------------
 (direct,0,0,0)
 (direct,0,0,0)
 (f,6,7,8)
 (e,3,4,5)
 (a,2,3,4)
 (g,0,9,0)
 (,,,)
 (,,,)
 (,,,)
 (,,,)
(10 rows)

预期的结果是(注意我不能做不同的，因为我会允许“非直接”记录的重复行):

 find_in_array_move
--------------------
 (direct,0,0,0)
 (f,6,7,8)
 (e,3,4,5)
 (a,2,3,4)
 (g,0,9,0)

有什么建议吗？

Answer 1

您不能从 plpgsql 函数中选择结果。您必须使用 RETURN QUERY:

CREATE OR REPLACE FUNCTION find_in_array_move(needle ANYELEMENT, haystack ANYARRAY,OUT cname text,out val1 numeric,out val2 numeric,out val3 numeric)
 RETURNS setof record AS $$
declare 
  i_move int;
begin
  for i_move in 2..5 loop
    RETURN QUERY
      SELECT $2[i-i_move][1],$2[i-i_move][2]::numeric,$2[i-i_move][3]::numeric,$2[i-i_move][4]::numeric
      FROM generate_series(array_lower($2,1), array_upper($2,1)) AS i
      WHERE $2[i][1] = $1;
  end loop;
  RETURN;
end;
$$ LANGUAGE plpgsql STABLE;

此外，根据您的 PostgreSQL 版本，您可能能够使用等效的 RETURNS TABLE，它比使用 OUT 参数更优雅且更易于维护:

CREATE OR REPLACE FUNCTION find_in_array_move(needle ANYELEMENT, haystack ANYARRAY)
  RETURNS TABLE (cname text, val1 numeric, val2 numeric, val3 numeric)
  AS $$
...

最后，如果您只是使用 generate_series 代替，则不需要 plpgsql LOOP:

CREATE OR REPLACE FUNCTION find_in_array_move(needle ANYELEMENT, haystack ANYARRAY)
  RETURNS TABLE (cname text, val1 numeric, val2 numeric, val3 numeric)
  AS $$
    SELECT $2[i-i_move][1],$2[i-i_move][2]::numeric,$2[i-i_move][3]::numeric,$2[i-i_move][4]::numeric
    FROM generate_series(2, 5) i_move,
         generate_series(array_lower($2,1), array_upper($2,1)) AS i
    WHERE $2[i][1] = $1
    ORDER BY i_move, i;
$$ LANGUAGE SQL STABLE;