Обсуждение: Default values in functions

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

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.

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