postgresql 返回 NULL 的空数组的 array_length()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6985350/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
array_length() of an empty array returning NULL
提问by JMagalhaes
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 应该在最短的时间内处理数千个调用。因此，我不希望为处理增加不必要的开销。我只是在寻找是否有任何方法可以在 LOOP 中“循环”一个空数组。
采纳答案by intgr
As always, if you want to have different behavior for NULL values, use the coalesceconstruct:
与往常一样，如果您想对 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 Nthdimension. 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;
回答by Erwin Brandstetter
Avoid the problem altogether by looping through the array with FOREACH, introduced with Postgres 9.1:
通过FOREACH使用 Postgres 9.1引入的数组循环来完全避免这个问题：
FOREACH i IN ARRAY
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 typically faster than looping in PostgreSQL.
根据您想要在循环内执行的操作，您可以完全避免循环并使用普通 SQL withunnest()代替。基于集合的操作通常比 PostgreSQL 中的循环更快。
Example:
例子：
RETURN QUERY
SELECT elem || 'foo'
FROM unnest() AS t(elem);
