Thread: Default values in functions
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.
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 );
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
Michael Lewis <mlewis@entrata.com> writes:
> 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.
I don't think your concerns have anything to do with the default
parameters, but rather with the operations the function is performing:
> SELECT icount ( pArrayToCheck ) = 0 INTO lReturnValue;
You didn't say what icount() is, but if it's the one from
contrib/intarray, it's STRICT meaning it'll return NULL,
not zero, for a null array input. Judging from your
expected_results, you want something more like
SELECT coalesce(icount(pArrayToCheck), 0) = 0 INTO lReturnValue;
(Or IOW, null::int[] is not at all the same thing as array[]::int[].)
> SELECT pTimeToDisplay AT TIME ZONE 'UTC' INTO pTimeToDisplay;
This is very unlikely to produce anything sane. The AT TIME ZONE
construct produces a timestamp-without-time-zone, which will then
be rotated per your TimeZone setting while coercing it back to
timestamp-with-time-zone for assignment to the output parameter.
You'll end up with a net rotation equal to your local zone's GMT offset.
For example:
postgres=# show timezone;
TimeZone
------------------
America/New_York
(1 row)
postgres=# select CURRENT_DATE::timestamptz;
current_date
------------------------
2021-12-29 00:00:00-05
(1 row)
postgres=# select CURRENT_DATE::timestamptz AT TIME ZONE 'UTC';
timezone
---------------------
2021-12-29 05:00:00
(1 row)
postgres=# select (CURRENT_DATE::timestamptz AT TIME ZONE 'UTC')::timestamptz;
timezone
------------------------
2021-12-29 05:00:00-05
(1 row)
I have no idea what you were hoping to accomplish there, so
I have no suggestion what to do instead.
regards, tom lane
On Wednesday, December 29, 2021, Michael Lewis <mlewis@entrata.com> wrote:
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.
There is not.
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.
That isn’t how it works. Absence is what is important. Null is not absence. As you showed, if you want nulls to be converted to defaults you can use coalesce.
David J.
On Wed, Dec 29, 2021, 4:34 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
That isn’t how it works. Absence is what is important. Null is not absence. As you showed, if you want nulls to be converted to defaults you can use coalesce.
Thanks sir. It seems unfortunate that there is not a way to indicate absence of the third parameter if I need to set a value for the fourth parameter.
On Wed, Dec 29, 2021, 4:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
You didn't say what icount() is, but if it's the one from
contrib/intarray, it's STRICT meaning it'll return NULL,
not zero, for a null array input.
Thanks for that. Very good to know.
(Or IOW, null::int[] is not at all the same thing as array[]::int[].)
Understood. My hope is to pass a parameter that gets overridden by the default so that I can pass other parameters that come after.
> SELECT pTimeToDisplay AT TIME ZONE'UTC' INTO pTimeToDisplay;
This is very unlikely to produce anything sane.
Sorry for that confusion. It was intended as an example of non default later in the list after a default.
Michael Lewis <mlewis@entrata.com> writes:
> Understood. My hope is to pass a parameter that gets overridden by the
> default so that I can pass other parameters that come after.
As David explained, function parameters don't work that way.
You could mechanize something like substitute-a-default-for-
null-input, but you'll have to do it with explicit logic.
regards, tom lane
Michael Lewis <mlewis@entrata.com> writes:
> Thanks sir. It seems unfortunate that there is not a way to indicate
> absence of the third parameter if I need to set a value for the fourth
> parameter.
The way to do that is to use named parameters and the associated
call syntax, ie something like
select myfunc(param1 => 42, param3 => 99);
regards, tom lane
On Wed, Dec 29, 2021, 5:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
The way to do that is to use named parameters and the associated
call syntax, ie something like
select myfunc(param1 => 42, param3 => 99);
Thanks very much. I have not seen that before.