Re: 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