BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade

The following bug has been logged on the website:

Bug reference: 19848
Logged by: Atish Prakash
Email address: 125atishprak(at)gmail(dot)com
PostgreSQL version: 18.6
Operating system: Linux x86_64
Description:

SQL-standard function bodies (BEGIN ATOMIC) handle parameter names wrongly
when
a function has OUT parameters before an input parameter, or an unnamed input
alongside named parameters. There are three visible symptoms.

1. A dump cannot be restored. An unnamed input with named OUT or TABLE
columns
deparses to an empty identifier:

CREATE TABLE s(a int PRIMARY KEY);
CREATE FUNCTION fa(int) RETURNS TABLE(x int) LANGUAGE sql
BEGIN ATOMIC SELECT a FROM s WHERE a = $1; END;
SELECT pg_get_functiondef('fa'::regproc);
-- ... WHERE (s.a = fa."");

Restoring pg_dump output gives:

ERROR: zero-length delimited identifier at or near """"

and the function is missing afterwards. These fail the same way:
f(int, OUT y int) and f(int, named int).

pg_upgrade is blocked too. With the function above in the old cluster,
pg_upgrade from 18.6 to 19rc1 stops at "Restoring database schemas in the
new
cluster" with the same error (exit status 1). The old server does the
deparsing, so a fix also needs to reach the old major version.

2. A valid function is rejected, and an OUT name is bound to the wrong
input.
Names are looked up by position among all parameters, including OUT ones:

CREATE FUNCTION a2(x int, OUT total int, y int) LANGUAGE sql
BEGIN ATOMIC SELECT x + y; END;
-- ERROR: column "y" does not exist

CREATE FUNCTION a1(x int, OUT total int, y int) LANGUAGE sql
BEGIN ATOMIC SELECT total; END;
SELECT a1(1, 2);
-- returns 2: "total" silently means input y

With AS $$ ... $$ the same bodies behave correctly: x + y works, and
total
gives 'column "total" does not exist'.

3. Deparsing shows the wrong name:

CREATE FUNCTION p1(a int, OUT b int, c int) LANGUAGE sql
BEGIN ATOMIC SELECT $1 * 10 + $2; END;
SELECT pg_get_function_sqlbody('p1'::regproc);
-- SELECT ((a * 10) + b) expected (a * 10) + c

Cause:
- interpret_function_parameter_list() appends every parameter to
inParameterNames_list, including OUT ones, although the list is then
indexed
by input position.
- print_function_sqlbody() passes the names from get_func_arg_info(), which
include OUT parameters, and get_parameter() indexes them by paramid. It
also
treats an empty name as a real name.

A possible patch is attached (0001-fix-sql-function-parameter-names.patch).
With
it, the cases above create, run and restore correctly on master, and all 239
core regression tests pass. It adds no new test.

Related, and not fixed by the patch: a parameter deparsed as function.name
can be
captured by a table of the same name, which changes results after a restore:

CREATE TABLE q2(a int);
INSERT INTO q2 VALUES (111);
CREATE FUNCTION q2(a int) RETURNS int LANGUAGE sql
BEGIN ATOMIC SELECT $1 FROM q2; END;
SELECT q2(5); -- 5 before dump and restore, 111 after

Found with dump and restore differential tests. I reproduced and checked the
result myself.