Re: [HACKERS] Naming of ORDINALITY column
On 24 June 2013 04:29, Josh Berkus j...@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 |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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Naming of ORDINALITY column (was: Re: Review: UNNEST (and other functions) WITH ORDINALITY)
OK, let's try to cover all the bases here in one go. First, the spec definition of WITH ORDINALITY simply says that the column name in the result is not equivalent to any other identifier in the same table primary (including the correlation name). It is clear that the intention of the spec is that any non-positional reference to this column (which is defined as being positionally last) requires an alias at some point, whether directly attached to the table primary or at an outer level. Second, all the documentation I've looked at for other databases that implement this feature (such as DB2, Teradata, etc.) takes it for granted that the user must always supply an alias, even though the syntax does not actually require one. None of the ones I've seen suggest that the ordinality column has a useful or consistent name if no alias is supplied. So, while clearly there's nothing stopping us from going beyond the spec and using a column name that people can refer to without needing an alias, it would be a significant divergence from common practice in other dbs. (iirc, it was my suggestion to David to use ?column? in the first place for this reason.) So as I see it the options are: 1. Stick with ?column? as a warning flag that you're not supposed to be using this without aliasing it to something. 2. Use some other fixed name like ordinality simply to allow people to do things like select ... from unnest(x) with ordinality; without having to bother to provide an alias, simply as a convenience, without regard for consistency with others. (This will result in a duplicate name if x is of a composite type containing a column called ordinality, so the caller will have to provide an alias in that specific case or get an ambiguous reference error. Similarly if using some other SRF which defines its own return column names.) 3. Generate an actually unique name (probably pointless) 4. Something else I haven't thought of. My vote remains with option 1 here; I don't think users should be encouraged to assume that the ordinality column will have a known name. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Naming of ORDINALITY column
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? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
