在 plpgsql 中循环数组维度 [英] Loop over array dimension in plpgsql

问题描述

在plpgsql中，我想从二维数组中一一获取数组内容.

In plpgsql, I want to get the array contents one by one from a two dimension array.

DECLARE
  m varchar[];
  arr varchar[][] := array[['key1','val1'],['key2','val2']];
BEGIN
  for m in select arr
  LOOP
    raise NOTICE '%',m;
  END LOOP;
END;

但是上面的代码返回:

{{key1,val1},{key2,val2}}

一行.我希望能够循环并调用另一个采用以下参数的函数:

in one line. I want to be able to loop over and call another function which takes parameters like:

another_func(key1,val1)

推荐答案

自 PostgreSQL 9.1 以来，就有了方便的 FOREACH:

Since PostgreSQL 9.1 there is the convenient FOREACH:

DO
$do$
DECLARE
   m   varchar[];
   arr varchar[] := array[['key1','val1'],['key2','val2']];
BEGIN
   FOREACH m SLICE 1 IN ARRAY arr
   LOOP
      RAISE NOTICE 'another_func(%,%)',m[1], m[2];
   END LOOP;
END
$do$

旧版本的解决方案:

DO
$do$
DECLARE
   arr varchar[] := '{{key1,val1},{key2,val2}}';
BEGIN
   FOR i IN array_lower(arr, 1) .. array_upper(arr, 1)
   LOOP
      RAISE NOTICE 'another_func(%,%)',arr[i][1], arr[i][2];
   END LOOP;
END
$do$

此外，对于 PostgreSQL 类型系统，varchar[] 和 varchar[][] 之间没有区别.我在此处进行了更详细的解释.

Also, there is no difference between varchar[] and varchar[][] for the PostgreSQL type system. I explain in more detail here.

DO 语句至少需要 PostgreSQL 9.0，并且 LANGUAGE plpgsql 是默认的(所以你可以省略声明).

The DO statement requires at least PostgreSQL 9.0, and LANGUAGE plpgsql is the default (so you can omit the declaration).

这篇关于在 plpgsql 中循环数组维度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！