Re: WITH ORDINALITY versus column definition lists

David Johnston <polobo(at)yahoo(dot)com> writes:
> Tom Lane-2 wrote
>> 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.

> Two options I came up with:

> 1) disallow any type specifier on the last item: t(f1 int, f2 text, o1)
> 2) add a new pseudo-type, "ord": t(f1 int, f2 text, o1 ord)

> I really like option #2.

I don't. Pseudo-types have a whole lot of baggage. #1 is a mess too.
And in either case, making coldef list items optional increases the number
of ways to make a mistake, if you accidentally omit some other column for
instance.

Basically the problem here is that it's not immediately obvious whether
the coldef list ought to include the ordinality column or not. The user
would probably guess not (since the system knows what type ordinality
should be). Unless he's trying to specify a column name for the ordinality
column, in which case he'll realize the syntax forces it to be there.
Any way you slice it, that's going to lead to confusion and bug reports.

The TABLE syntax is really a vastly better solution for this. So I'm
thinking my #1 is the best answer, assuming we can come up with a good
error message. My first attempt would be

ERROR: WITH ORDINALITY cannot be used with a column definition list
HINT: Put the function's column definition list inside TABLE() syntax.

Better ideas?

regards, tom lane