Re: pg_get__*_ddl consolidation

On Mon, Apr 6, 2026, at 5:24 PM, Jelte Fennema-Nio wrote:
> On Mon, 6 Apr 2026 at 19:10, Euler Taveira <euler@eulerto.com> wrote:
>> although we already use this style
>> in some of the backend functions -- e.g. pg_logical_slot_*_changes()).
>
> Thanks for the additional context. I didn't know about
> pg_logical_slot_*_changes using this style. I searched the docs
> locally and cannot find any other functions that use this style. I
> think what makes pg_logical_slot_*_changes special, is that it passes
> these options to the plugin. The plugin can define any valid options,
> and postgres core cannot know what they are. I think this approach
> makes sense for those functions because of that, but the ddl functions
> don't pass the options to a plugin, so that argument does not apply
> here.
>

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.

>> I also consider your approach but decided not to use it. The argument against
>> named arguments is that you cannot add new argument *without* a DEFAULT value;
>> if you do, all existing functions will fail.
>
> I'm not sure what kind of change you're referring to here. I don't
> understand how variadic options allow you to add a required argument
> to an existing function without breaking existing callers. Could you
> give a concrete example of a change that the VARIADIC allows, but the
> named arguments don't?
>

Indeed. My sentence was confused. I want to say that the regular argument
list is not as flexible as the VARIADIC argument. Once you have an argument
with DEFAULT, you cannot have a next argument *without* DEFAULT. For VARIADIC
arguments, this restriction does not exist; there is no need to change the
function signature. The argument manipulation (default value, non null)
happens inside the function.

postgres=# create function foo(arg1 int default 0, arg2 int) returns int as $$ begin return arg1 + arg2; end; $$ language plpgsql;
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...

It means these functions cannot add a new required argument (without DEFAULT).
Unless you change the current order of the arguments and put arg2 argument
before arg1. Doing that you could silently break existing function calls (if
argument type is that same as the existing one).

postgres=# create function foo(arg1 int default 0, arg2 int default 0) returns int as $$ begin return arg1 + arg2; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select foo(arg1 => 5, arg2 => 8);
 foo 
-----
  13
(1 row)

postgres=# select foo(5, 8);
 foo 
-----
  13
(1 row)

postgres=# -- include new argument 
postgres=# drop function foo(int, int);
DROP FUNCTION
postgres=# create function foo(arg3 int, arg1 int default 0, arg2 int default 0) returns int as $$ begin return (arg1 + arg2) * arg3; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select foo(5, 8);
 foo 
-----
  40
(1 row)

Of course, if you are using named arguments an error is emitted.

postgres=# select foo(arg1 => 5, arg2 => 8);
ERROR:  function foo(arg1 => integer, arg2 => integer) does not exist
LINE 1: select foo(arg1 => 5, arg2 => 8);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

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).

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.)


>> You also need to create another
>> function with a different list of arguments to support a new option.
>
> I don't understand this either. We often add new optional arguments to
> existing functions in a new major release. e.g. pg_start_backup got
> the exclusive argument in PG9.6. Or do you mean something else here?

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


[1] https://www.postgresql.org/docs/18/functions-admin.html#FUNCTIONS-ADMIN-STATSMOD


-- 
Euler Taveira
EDB   https://www.enterprisedb.com/