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*
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 |