The foo() function bellow shows the problem, never do the ELSE in the CASE statement. The foo_null() is the same function, changing only the returning value.
CREATE FUNCTION foo(
use_order boolean DEFAULT false
) RETURNS text[] language SQL IMMUTABLE
AS $f$
SELECT CASE
WHEN use_order THEN array_agg(x ORDER BY x)
ELSE array_agg(x)
END
FROM unnest('{x,y,a,b}'::text[]) t(x)
$f$;
--- Check CASE is working returning null
CREATE FUNCTION foo_null(
use_order boolean DEFAULT false
) RETURNS text[] language SQL IMMUTABLE
AS $f$
SELECT CASE
WHEN use_order THEN NULL ELSE array_agg(x)
END
FROM unnest('{x,y,a,b}'::text[]) t(x)
$f$;
select foo(), foo(true) foo_true ,foo(false) foo_false, foo_null(), foo_null(true) foo_null_true;
foo | foo_true | foo_false | foo_null | foo_null_true
-----------+-----------+-----------+-----------+---------------
{a,b,x,y} | {a,b,x,y} | {a,b,x,y} | {x,y,a,b} |
The result shows same return for any parameter using foo(), so never do ELSE. But foo_null() is working fine.
Does not make sense.