Re: Naming of ORDINALITY column

On 24 June 2013 04:29, Josh Berkus <josh@agliodbs.com> wrote:
> On 06/23/2013 08:00 PM, Andrew Gierth wrote:
>> OK, let's try to cover all the bases here in one go.
>
>> 1. Stick with "?column?" as a warning flag that you're not supposed to
>> be using this without aliasing it to something.
>
> How do I actually supply an alias which covers both columns?  What does
> that look like, syntactically?
>

There are a number of possible syntaxes:

SELECT unnest, "?column?" FROM unnest(ARRAY['x','y']) WITH ORDINALITY;
or
SELECT unnest.unnest, unnest."?column?" FROM unnest(ARRAY['x','y'])
WITH ORDINALITY;unnest | ?column?
--------+----------x      |        1y      |        2
(2 rows)


SELECT t, "?column?" FROM unnest(ARRAY['x','y']) WITH ORDINALITY AS t;
or
SELECT t.t, t."?column?" FROM unnest(ARRAY['x','y']) WITH ORDINALITY AS t;t | ?column?
---+----------x |        1y |        2
(2 rows)


SELECT val, "?column?" FROM unnest(ARRAY['x','y']) WITH ORDINALITY AS t(val);
or
SELECT t.val, t."?column?" FROM unnest(ARRAY['x','y']) WITH ORDINALITY
AS t(val);val | ?column?
-----+----------x   |        1y   |        2
(2 rows)


SELECT val, ord FROM unnest(ARRAY['x','y']) WITH ORDINALITY AS t(val, ord);
or
SELECT t.val, t.ord FROM unnest(ARRAY['x','y']) WITH ORDINALITY AS t(val, ord);val | ord
-----+-----x   |   1y   |   2
(2 rows)

My suggestion was to replace "?column?" with ordinality wherever it
appears above, for the user's convenience, but so far more people
prefer "?column?" as a way of indicating that you're supposed to
provide an alias for the column.

If that's what people prefer, I don't mind --- it's still going to be
a very handy new feature.

Regards,
Dean