Default values in functions

[Michael Lewis <mlewis@xxxxxxxxxxx>]

I am on PG 13.4 and found some surprising behavior with default values. Can anyone give insight why the last two cases (especially the last one) do not give the result I expected? If I uncomment the line to set pArrayToCheck with coalesce, then it gives the expected results though.

If I can somehow pass "use default for this parameter" to functions like I can for insert statements, then that would be great to know. Generally, the use case I am looking at is having a function with a few required parameters leading and then many optional and I'd like to ensure the optional ones get the default set if a value is used that "is not distinct from null" basically. Hopefully the example is clear.

CREATE OR REPLACE FUNCTION public.check_if_input_is_empty_array( pArrayToCheck integer[] DEFAULT ARRAY[]::integer[], inout pTimeToDisplay timestamptz DEFAULT CURRENT_DATE::timestamptz, out lReturnValue boolean )
LANGUAGE plpgsql
AS $function$
BEGIN
    --pArrayToCheck = COALESCE( pArrayToCheck::integer[], ARRAY[]::integer[] );
   
    SELECT icount ( pArrayToCheck ) = 0 INTO lReturnValue;
    SELECT pTimeToDisplay AT TIME ZONE 'UTC' INTO pTimeToDisplay;
   
END;
$function$;

select ( check_if_input_is_empty_array() ).*, true as expected_result
UNION ALL
select ( check_if_input_is_empty_array( pArrayToCheck, pTimeToDisplay ) ).*, expected_result
from(
values
( ARRAY[]::int[], CURRENT_DATE + interval '1 hour', true ),
( ARRAY[1]::int[], CURRENT_DATE + interval '2 hour', false ),
( null::int[] , CURRENT_DATE + interval '3 hour', true ),
( null , CURRENT_DATE + interval '4 hour', true )
)AS sub ( pArrayToCheck, pTimeToDisplay, expected_result );

Michael Lewis  |  Database Engineer

Entrata