Re: Default values in functions

Michael Lewis <mlewis(at)entrata(dot)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