返回NULL的空数组的array_length（）

I'm developing some stored proceduces in PL/pgSQL and some of them are giving me some problems. The sprocs I'm developing receive by parameter an array which I use in a FOR LOOP to get all its elements. To define the upper bound of the FOR LOOP I use the array_length function.

我正在PL / pgSQL中开发一些存储过程，其中一些正在给我一些问题。我正在开发的sprocs通过参数接收一个数组，我在FOR LOOP中使用它来获取它的所有元素。要定义FOR LOOP的上限，我使用array_length函数。

FOR i IN 1..array_length(array,1) LOOP

   --array[i] something in here

END LOOP;

The problems occurs when I give to the sprocs an empty array. Instead of not entering the cycle, the sproc simply returns an error, stating that the upper bound of the FOR LOOP is NULL. Shouldn’t it be 0?

当我向sprocs提供一个空数组时会出现问题。 sproc只是返回一个错误，而不是不进入循环，说明FOR LOOP的上限是NULL。不应该是0吗？

Am I doing anything wrong with the FOR LOOP?

我对FOR LOOP有什么不妥吗？

Is there any other way to use the same bounds in a LOOP without it returning NULL when using an empty array?

有没有其他方法在LOOP中使用相同的边界而不使用空数组时返回NULL？

Note: I know I can always use a condition before the LOOP, like this:

注意：我知道我总是可以在LOOP之前使用条件，如下所示：

IF array_length(array,1) IS NOT NULL THEN

but the problem is: This sproc is supposed to process thousands of calls in the shortest amount of time. As so, I'm not looking to something that adds an unnecessary overhead to the processing. I'm just looking if there is any way to “cycle” an empty array in a LOOP.

但问题是：这个sproc应该在最短的时间内处理数千个电话。因此，我并不期待为处理增加不必要的开销。我只是在寻找是否有办法在循环中“循环”一个空数组。

2 个解决方案

#1

As always, if you want to have different behavior for NULL values, use the coalesce construct:

与往常一样，如果要对NULL值使用不同的行为，请使用coalesce构造：

FOR i IN 1..coalesce(array_length(array, 1), 0) LOOP
    RAISE NOTICE '%', array[i];
END LOOP;

As for the return value: array_length(x, N) returns the number of elements in Nth dimension. Since an empty array has no dimensions, it returns NULL. You're right that it's counterintuitive if you only consider simple arrays, but makes sense for multi-dimensional arrays.

至于返回值：array_length（x，N）返回第N维中的元素数。由于空数组没有维度，因此返回NULL。你是对的，如果你只考虑简单的数组，它是违反直觉的，但对于多维数组是有意义的。

Edit: Like Erwin Brandstetter wrote in the comments, it's more correct to use array_lower/upper to loop over array indices. These will work for arrays that are not 1-based. These also take a dimension argument and require coalesce:

编辑：像Erwin Brandstetter在评论中写道，使用array_lower / upper循环数组索引更为正确。这些将适用于非基于1的阵列。这些也需要一个维度参数并要求合并：

FOR i IN coalesce(array_lower(array, 1), 1)..coalesce(array_upper(array, 1), 1) LOOP
    RAISE NOTICE '%', array[i];
END LOOP;

#2

Avoid the problem altogether by looping through the array with FOREACH, introduced with Postgres 9.1:

通过使用Postgres 9.1引入的FOREACH循环遍历数组，完全避免了这个问题：

FOREACH i IN ARRAY $1
LOOP
   -- do something
END LOOP;

Depending on what you want to do inside the loop, you might be able to avoid looping altogether and use plain SQL with unnest() instead. Set-based operations are generally faster than looping in PostgreSQL.

根据您在循环中要执行的操作，您可以完全避免循环并使用带有unexst（）的纯SQL。基于集合的操作通常比PostgreSQL中的循环更快。

Example:

例：

RETURN QUERY
SELECT elem || 'foo'
FROM unnest($1) AS t(elem)

#1