Default values in functions

From: Michael Lewis <mlewis(at)entrata(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Default values in functions
Date: 2021-12-29 22:59:10
Message-ID: CAHOFxGpqLtuayEUxVZ5wmpdTp+y6m_VbVWw=zhCrNi1_TcEeQQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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*

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-12-29 23:31:04 Re: Default values in functions
Previous Message David G. Johnston 2021-12-29 21:46:44 Re: csv copy error