Re: Naming of ORDINALITY column

On 24 June 2013 04:29, Josh Berkus <josh(at)agliodbs(dot)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 | 1
y | 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 | 1
y | 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 | 1
y | 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 | 1
y | 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