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.
#2 but I am hoping to be able to make the definition of the column optional.
One possibility is that if you do want to provide an alias you have to make
it clear that the coldeflist item in question is only valid for a with
ordinality column alias. Otherwise the entire coldeflist is used to alias
the record-type output and the ordinality column is provided its default
name.
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. It makes it perfectly clear, entirely within the
coldeflist SQL, that the last column is different and in this case optional
both in the sense of providing an alias and also the user can drop the whole
ordinality aspect of the call as well. The system does not need to be told,
by the user, the actual type of the ordinality column. And given that I
would supposed most people would think to use "int" or "bigint" before using
"int8" the usability there is improved once they need and then learn that to
alias the ordinality column they use the "ord" type which would internally
resolve to the necessary output type.
Option one is somewhat simpler but the slight added verbosity makes reading
the SQL coldeflist easier, IMO, since you are already scanning name-type
pairs and recognizing the missing type is, for me, harder than reading off
"ord" and recalling its meaning.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/WITH-ORDINALITY-versus-column-definition-lists-tp5779443p5779449.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
