在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 起，便捷的

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$

旧版本的解决方案:

Solution for older versions:

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屋！