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

To note: '{}'::int[] IS DISTINCT FROM null

'{}' is an "empty array". An empty array is not the same as null. But an empty array has unset internal boundaries, hence you get null for any of these auxiliary functions:

SELECT array_length('{}'::int[], 1)  -- null
     , array_upper('{}'::int[], 1)   -- null
     , array_lower('{}'::int[], 1);  -- null

And that's what this question is about.

Solution for empty arrays

To avoid the problem with empty arrays altogether, loop through the array with FOREACH, introduced with Postgres 9.1:

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

Solution for empty arrays and null

If null input is also possible, you need to do more. For example, if null shall be treated like an empty array ( = do not enter the loop at all), throw in COALESCE:

FOREACH i IN ARRAY COALESCE($1, '{}')
LOOP
   -- do something
END LOOP;

Related:

• Best way to check for "empty or null value"

Pure SQL is often better

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

See:

• Postgres FOR LOOP