Re: WITH ORDINALITY versus column definition lists

Tom Lane-2 wrote
> David Johnston <

> polobo@

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

I'll have to trust on the baggage/mess conclusion but if you can distinctly
and un-ambigiously identify the coldeflist item that is to be used for
ordinality column aliasing then the mistakes related to the
function-record-coldeflist are the same as now. There may be more (be still
quite few I would think) ways for the user to make a mistake but the syntax
ones are handled anyway and so if the others can be handled reasonably well
the UI for the feature becomes more friendly.

IOW, instead of adding int8 and ignoring it we poll the last item,
conditionally discard it (like the int8 case), then handle the possibly
modified structure as planned.

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

Yes, if the column is not made optional somehow then I dislike option #2

> 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?

Works for me if #1 is implemented.

Just to clarify we are still allowing simple aliasing:

select * from generate_series(1,2) with ordinality as t(f1,f2);

Its only when the output of the function is "record" does the restriction of
placing the record-returning function call into TABLE (if you want ordinals)
come into play.

select * from table(array_to_set(array['one', 'two']) as (f1 int,f2 text))
with ordinality as t(a1,a2,a3);

If we could do away with having to re-specify the record-aliases in the
outer layer (a1, a2) then I'd be more understanding but I'm thinking that is
not possible unless you force a single-column alias definition attached to
WITH ORDINALITY to mean alias the ordinality column only.

On the plus side: anyone using record-returning functions is already dealing
with considerable verbosity so this extra bit doesn't seem to be adding that
much overhead; and since the alias - t(a1,a2,a3) - is optional if you don't
care about aliasing the with ordinal column the default case is not that
verbose (just add the surrounding TABLE).

I feel like I need a flow-chart for #1...

With #2 (w/ optional) you can add in an alias for the ordinality column
anyplace you would be specifying a coldeflist OR alias list. Favoring the
pseudo-type solution is the fact that given the prior sentence if you place
"o1 ord" in the wrong place it is possible to generate an error like "with
ordinality not present for aliasing".

#1 is simpler to implement and does not preclude #2 in the future.

Possible #3?

Not sure if this is possible at this point but really the alias for the
ordinality column would be attached directly to the ordinality keyword.

e.g., ...) with ordinality{alias} as t(a1, a2)

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/WITH-ORDINALITY-versus-column-definition-lists-tp5779443p5779468.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.