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.

Answer 1

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; 

Answer 2

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);