WITH ORDINALITY versus column definition lists - Mailing list pgsql-hackers

Consider the following case of a function that requires a column
definition list (example borrowed from the regression tests):

create function array_to_set(anyarray) returns setof record as $$ select i AS "index", $1[i] AS "value" from
generate_subscripts($1,1) i
 
$$ language sql strict immutable;

select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text);

What if you want to add ordinality to that?  In HEAD you get:

regression=# select * from array_to_set(array['one', 'two']) with ordinality as t(f1 int,f2 text);
ERROR:  WITH ORDINALITY is not supported for functions returning "record"
LINE 1: select * from array_to_set(array['one', 'two']) with ordinal...                     ^

which is a restriction imposed by the original WITH ORDINALITY patch.
The currently-submitted patch removes this restriction (although not the
documentation about it :-(), and what you get is

regression=# select * from array_to_set(array['one', 'two']) with ordinality as t(f1 int,f2 text);f1 | f2  | ordinality

----+-----+------------ 1 | one |          1 2 | two |          2
(2 rows)

Notice that the coldef list doesn't include the ordinality column, so in
this syntax there is no way to choose a different name for the ordinality
column.  The new TABLE syntax provides an arguably-saner solution:

regression=# select * from table(array_to_set(array['one', 'two']) as (f1 int,f2 text)) with ordinality;f1 | f2  |
ordinality
 
----+-----+------------ 1 | one |          1 2 | two |          2
(2 rows)

regression=# select * from table(array_to_set(array['one', 'two']) as (f1 int,f2 text)) with ordinality as
t(a1,a2,a3);a1| a2  | a3 
 
----+-----+---- 1 | one |  1 2 | two |  2
(2 rows)

Now, it seems to me that putting WITH ORDINALITY on the same syntactic
level as the coldeflist is pretty confusing, especially since it behaves
differently than WITH ORDINALITY with a simple alias list:

regression=# select * from generate_series(1,2) with ordinality as t(f1,f2);f1 | f2 
----+---- 1 |  1 2 |  2
(2 rows)

Here, the alias list does extend to the ordinality column.

It seems to me that we don't really want this behavior of the coldeflist
not including the ordinality column.  It's operating as designed, maybe,
but it's unexpected and confusing.  We could either

1. Reinsert HEAD's prohibition against directly combining WITH ORDINALITY
with a coldeflist (with a better error message and a HINT suggesting that
you can get what you want via the TABLE syntax).

2. Change the parser so that the coldeflist is considered to include the
ordinality column, for consistency with the bare-alias case.  We'd
therefore insist that the last coldeflist item be declared as int8, and
then probably have to strip it out internally.

Thoughts?
        regards, tom lane