array_length() of an empty array returning NULL

Question

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.

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?

Am I doing anything wrong with the FOR LOOP?

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

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

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.

intgr · Accepted Answer · 2013-09-04T09:23:22.950

28

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

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.

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:

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

edited Sep 04 '13 at 09:23

answered Aug 08 '11 at 18:39

intgr

19,834
5
59
69

1

This form is still not 100 % safe since Postgres supports [arbitrary array subscripts](http://stackoverflow.com/questions/12011569/normalize-array-subscripts-for-1-dimensional-array-so-they-start-with-1). You would have to use `array_lower()` and `array_upper()` to be sure. – Erwin Brandstetter Aug 05 '13 at 19:10
3

for the upper bound, shouldn't be coalesce(array_upper(array, 1), 0) ? As 1 .. 1 will still make it to enter the loop once. – shinobi Jun 19 '16 at 06:43

Erwin Brandstetter · Answer 2 · 2019-03-22T15:36:21.250

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

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 typically faster than looping in PostgreSQL.

Example:

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

array_length() of an empty array returning NULL

2 Answers2