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

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.