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*
