Re: pg_get__*_ddl consolidation - Mailing list pgsql-hackers

On Tue, 7 Apr 2026 at 05:44, Euler Taveira <euler@eulerto.com> wrote:
> There are other functions. See pg_restore_extended_stats() [1] and related
> functions. If you are looking for flexibility, this key-value pair arguments is
> one of the ways to achieve it.

Ah, I missed that one indeed my search didn't find it because it used
type "any" for the variadic. That makes the usage nicer imo than the one
used here, because you can actually give booleans to it (not only 'true'
& 'false' as strings).

Looking at those examples I foresee another big downside to the VARIADIC
approach. It's impossible for an SQL autoformatter to make the function
call look nice, because it does not know the two arguments are supposed
to be together. They will all be put on a single line. The only way to
get a nicely looking function call is hand-formatting the code.

> postgres=# create function foo(arg1 int default 0, arg2 int) returns int as $$ begin return arg1 + arg2; end; $$
languageplpgsql; 
> ERROR:  input parameters after one with a default value must also have defaults
> LINE 1: create function foo(arg1 int default 0, arg2 int) returns in...

You don't give an example how VARIADIC gives you the ability to make
that behave differently. But I guess you mean that:

SELECT foo();

would start erroring "with 'arg2' is required" and users would have to do

SELECT foo('arg2', 123);

There's a pretty simple way to get that same behaviour for the named
arguments approach though. Simply use DEFAULT NULL as the default for
arg2, and make it nonstrict. Then you can check for NULL in the
implementation and throw an error, just like you would do for the
VARIADIC version. With that

SELECT foo(); --errors
SELECT foo(arg2 => 123) -- works

So I don't see how this VARIADIC differs in this case.

In any case it seems unlikely to me that we want to ever add new
required arguments to these functions. Simply for backwards
compatibility reasons that sounds like a huge hassle that we'll probably
want to avoid by giving any new arguments a default. So even if there
was a difference, I don't really consider that a useful benefit of the
VARIADIC approach.

> The VARIADIC argument forces you to always specify the argument name; that's a
> good thing. The regular argument list requires you to remember the order of the
> arguments (unless you are using named arguments).

I definitely agree with this. But I think that's solvable in practice by
having examples in the docs showing how to use named arguments for these
functions (see attached v6). That way most users will use that named
argument syntax as opposed to the positional one.

> It is just a few arguments for the current functions but I predict that
> pg_get_table_dll may have a dozen of arguments. IMO the VARIADIC approach is
> superior when you want several options. The function call is smaller in
> comparison to your proposal. (Let's say you want to specify the last argument
> value. Inform all the other default arguments plus the argument you want to
> change. For VARIADIC, specify only the argument you want to change.)

If you use named arguments to call the function, then all of this
doesn't matter. And actually the VARIADIC can be more confusing.
Especially with many arguments because it can be unclear which of the
arguments is a key and which one is a value.

And even for few arguments a reader can be confused, if the reader
doesn't realize that the arguments are interpreted as key value pairs.
For instance, I'd say that for a call like below, it's not obvious that
'foreign_keys' and 'all' are a pair. I'd have to look at the function
docs to realize that these are not two separate arguments (one set to
'foreign_keys', and the other to 'all'):

select pg_get_table_ddl('mytable', 'foreign_keys', 'all');

while with the named argument syntax makes that's immediately clearer:

select pg_get_table_ddl('mytable', foreign_keys => 'all');

> I meant modifying the pg_proc.dat every time a new argument is added.

Sure, but I don't understand why that would be problem. We do that all
the time in major releases. Even with the VARIADIC approach, I don't
think we should be adding optional arguments in minor releases.

So to summarize (from my biased viewpoint) I think the downsides are:
1. Uncommon calling convention: only pg_restore_*_stats and
   pg_logical_slot_*_changes use it, while all other functions support
   named parameters.
2. Needs custom option parsing logic
3. More characters to type because you have to quote booleans, integers
   and argument names.
4. Requires functions to be marked as NOSTRICT, which then needs
   additional NULL handling
5. It can be unclear to a reader of a query that the function arguments
   should be interpreted as key-value pair
6. Breaks auto formatting

And the benefit:
1. Forces people to specify the argument name

I don't think those benefits outweigh the downsides.