Thread: Patch: UNNEST (and other functions) WITH ORDINALITY
Folks,
Please find attached a patch which implements the SQL standard
UNNEST() WITH ORDINALITY. It doesn't stop there. Any function call
in a FROM clause can now take WITH ORDINALITY, which appends a counter
(ordinality) column to the columns the function outputs and produce
results like this:
postgres@postgres:5493=# select * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
ls | n
-----------------+----
pg_serial | 1
pg_twophase | 2
postmaster.opts | 3
pg_notify | 4
postgresql.conf | 5
pg_tblspc | 6
logfile | 7
base | 8
postmaster.pid | 9
pg_ident.conf | 10
global | 11
pg_clog | 12
pg_snapshots | 13
pg_multixact | 14
PG_VERSION | 15
pg_xlog | 16
pg_hba.conf | 17
pg_stat_tmp | 18
pg_subtrans | 19
(19 rows)
TBD: polish the docs, add regression tests, possibly add psql support.
Thanks to Andrew (RhodiumToad) Gierth for the hard work designing and
implementing this feature.
Tom, is there some better way to do this?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachment
On Tue, Jan 22, 2013 at 10:29:43PM -0800, David Fetter wrote: > Folks, > > Please find attached a patch which implements the SQL standard > UNNEST() WITH ORDINALITY. Added to CF4. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter wrote: > On Tue, Jan 22, 2013 at 10:29:43PM -0800, David Fetter wrote: > > Folks, > > > > Please find attached a patch which implements the SQL standard > > UNNEST() WITH ORDINALITY. > > Added to CF4. Surely you meant CF 2013-Next (i.e. first commit of 9.4 cycle). -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Jan 23, 2013 at 03:12:37PM -0300, Alvaro Herrera wrote: > David Fetter wrote: > > On Tue, Jan 22, 2013 at 10:29:43PM -0800, David Fetter wrote: > > > Folks, > > > > > > Please find attached a patch which implements the SQL standard > > > UNNEST() WITH ORDINALITY. > > > > Added to CF4. > > Surely you meant CF 2013-Next (i.e. first commit of 9.4 cycle). I see that that's what I did, but given that this is a pretty small feature with low impact, I'm wondering whether it should be on CF4. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, Jan 23, 2013 at 10:15:27AM -0800, David Fetter wrote: > On Wed, Jan 23, 2013 at 03:12:37PM -0300, Alvaro Herrera wrote: > > David Fetter wrote: > > > On Tue, Jan 22, 2013 at 10:29:43PM -0800, David Fetter wrote: > > > > Folks, > > > > > > > > Please find attached a patch which implements the SQL standard > > > > UNNEST() WITH ORDINALITY. > > > > > > Added to CF4. > > > > Surely you meant CF 2013-Next (i.e. first commit of 9.4 cycle). > > I see that that's what I did, but given that this is a pretty small > feature with low impact, I'm wondering whether it should be on CF4. The diff is 1.2k and has no discussion. It should be in CF 2013-Next. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Wed, Jan 23, 2013 at 02:40:45PM -0500, Bruce Momjian wrote: > On Wed, Jan 23, 2013 at 10:15:27AM -0800, David Fetter wrote: > > On Wed, Jan 23, 2013 at 03:12:37PM -0300, Alvaro Herrera wrote: > > > David Fetter wrote: > > > > On Tue, Jan 22, 2013 at 10:29:43PM -0800, David Fetter wrote: > > > > > Folks, > > > > > > > > > > Please find attached a patch which implements the SQL standard > > > > > UNNEST() WITH ORDINALITY. > > > > > > > > Added to CF4. > > > > > > Surely you meant CF 2013-Next (i.e. first commit of 9.4 cycle). > > > > I see that that's what I did, but given that this is a pretty small > > feature with low impact, I'm wondering whether it should be on CF4. > > The diff is 1.2k and has no discussion. It's been up less than a day ;) > It should be in CF 2013-Next. OK :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Jan 22, 2013 at 10:29:43PM -0800, David Fetter wrote: > Folks, > > Please find attached a patch which implements the SQL standard > UNNEST() WITH ORDINALITY. It doesn't stop there. Any function call > in a FROM clause can now take WITH ORDINALITY, which appends a counter > (ordinality) column to the columns the function outputs and produce > results like this: Next revision of the patch, now with more stability. Thanks, Andrew! Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachment
On Fri, Feb 08, 2013 at 01:12:15PM -0800, David Fetter wrote: > On Tue, Jan 22, 2013 at 10:29:43PM -0800, David Fetter wrote: > > Folks, > > > > Please find attached a patch which implements the SQL standard > > UNNEST() WITH ORDINALITY. It doesn't stop there. Any function call > > in a FROM clause can now take WITH ORDINALITY, which appends a counter > > (ordinality) column to the columns the function outputs and produce > > results like this: > > Next revision of the patch, now with more stability. Thanks, Andrew! Rebased vs. git master. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate