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
