plpgsql function taking a multi-dimensional array as parameter

Question

I'm trying to run a plpgsql function on a multi-dimensional array column (int[][]) in a select query.

The function is as such:

CREATE OR REPLACE FUNCTION public.reduce_to_percentages(int[][])
RETURNS float[] AS
$function$
DECLARE
  s int[];
  a float[];
BEGIN
    FOREACH s SLICE 1 IN ARRAY $1 LOOP
        a := a || s[2]::float / s[1]::float;
    END LOOP;
    RETURN a;
END;
$function$
LANGUAGE plpgsql VOLATILE;

The following query works:

SELECT reduce_to_percentages(ARRAY[ARRAY[100, 20], ARRAY[300, 50]]);

So does the following query:

SELECT reduce_to_percentages((SELECT counts FROM objects LIMIT 1));

But the following query provides the function with a null value, and causes an exception when trying to FOREACH over $1:

SELECT reduce_to_percentages(counts) FROM objects;

Answer 1

You can fix the error by checking for NULL values like @klin provided.
But that's putting lipstick on a pig. Rewrite the function to replace the procedural loop with a set-based solution. Typically faster (especially when used in the context of an outer query), simpler and automatically null-safe:

CREATE OR REPLACE FUNCTION public.reduce_to_percentages(int[])
  RETURNS float[] AS
$func$
SELECT ARRAY (SELECT $1[d][2]::float / $1[d][1]
              FROM   generate_subscripts($1,1) d)
$func$  LANGUAGE sql IMMUTABLE;

Related:

• Unnest array by one level

Notes:

• Casting the dividend to float is enough. float / integer returns float automatically.

• There is no data type int[][]. Just int[]. Postgres allows the notation, but ignores additional array dimensions for array types, since the data type itself is the same for all dimensions. Details:

  • mapping postgresql text[][] type and Java type

• Make the function IMMUTABLE (since it is) for better performance and to allow indexing. Related:

  • How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
  • Does PostgreSQL support "accent insensitive" collations?

---

Alternatively, you could declare your function as STRICT (synonym: RETURNS NULL ON NULL INPUT). Minor difference: it returns NULL for NULL input, not an empty array ('{}') like the above.

CREATE OR REPLACE FUNCTION public.reduce_to_percentages(int[][])
  RETURNS float[] AS
$function$
 ...
$function$ LANGUAGE plpgsql IMMUTABLE STRICT;

The manual:

RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

But the simple SQL function above is preferable for multiple reasons.

Answer 2

In the function body just check if the argument is not null. If you want to return an empty array for a null argument then add an initial value for the variable a in declare section (otherwise the function will return null).

CREATE OR REPLACE FUNCTION public.reduce_to_percentages(int[][])
RETURNS float[] AS
$function$
DECLARE
  s int[];
  a float[] = '{}';     -- initial value
BEGIN
    IF $1 IS NOT NULL THEN
        FOREACH s SLICE 1 IN ARRAY $1 LOOP
            a := a || s[2]::float / s[1]::float;
        END LOOP;
    END IF;
    RETURN a;
END;
$function$
LANGUAGE plpgsql VOLATILE;

---

I have done a simple test. This is a modest setup:

create table test (val int[]);
insert into test
select (array[array[ri(100), ri(100)], array[ri(100), ri(100)]])
from generate_series(1, 1000000);

ri(100) is my function returning random integer between 1-100. So the table contains a million rows with an array of two arrays of two integers in one column. I tried the test to be as simple and typical as possible.

The following query has been executed 10 times with Erwin's function and 10 times with my variant:

select sum(v[1]), sum(v[2])
from (
    select reduce_to_percentages(val) v
    from test
    ) s;

Average execution time of ten tests:

• Erwin's function 11940 ms
• klin's function 4750 ms

Maybe my function is a pig with lipstick on its snout but it is a speedy one.