在PL / pgSQL中迭代整数[]

I am trying to loop through an integer array (integer[]) in a plpgsql function. Something like this:

我试图在plpgsql函数中循环遍历整数数组（integer []）。像这样的东西：

declare
    a integer[] = array[1,2,3];
    i bigint;
begin
    for i in a
loop 
    raise notice "% ",i;
end loop;
return true;
end

In my actual use case the integer array a is passed as parameter to the function. I get this error:

在我的实际用例中，整数数组a作为参数传递给函数。我收到此错误：

ERROR:  syntax error at or near "$1"
LINE 1:   $1

How to loop through the array properly?

如何正确循环数组？

1 个解决方案

#1

DECLARE
   a integer[] := array[1,2,3];
   i integer;                   -- int, not bigint!
BEGIN
FOR i IN 1 .. array_upper(a, 1)
LOOP
   RAISE NOTICE '%', a[i];      -- single quotes!
END LOOP;
RETURN TRUE;
END

Or try the new FOREACH in PostgreSQL 9.1:

或者在PostgreSQL 9.1中尝试新的FOREACH：

FOREACH i IN ARRAY a
LOOP 
   RAISE NOTICE '%', i;
END LOOP;

However, set-based solutions with generate_series() or unnest() are often faster than looping for big sets.

但是，使用generate_series（）或unnest（）的基于集合的解决方案通常比大集合的循环更快。

Basic examples:

基本示例：

PostgreSQL: Frequency Table Expansion
PostgreSQL：频率表扩展
Select each month between a start and end date
选择开始日期和结束日期之间的每个月

Search the tags generate-series or unnest for more.

搜索标签generate-series或unnest以获取更多信息。

#1

DECLARE
   a integer[] := array[1,2,3];
   i integer;                   -- int, not bigint!
BEGIN
FOR i IN 1 .. array_upper(a, 1)
LOOP
   RAISE NOTICE '%', a[i];      -- single quotes!
END LOOP;
RETURN TRUE;
END