Re: Patch: UNNEST (and other functions) WITH ORDINALITY

Lists: pgsql-hackers
From: David Fetter <david(at)fetter(dot)org>
To: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Patch: UNNEST (and other functions) WITH ORDINALITY
Date: 2013-01-23 06:29:43
Message-ID: 20130123062943.GB26369@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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 Content-Type Size
ordinality_20130122_2220.patch text/plain 39.4 KB
From: David Fetter <david(at)fetter(dot)org>
To: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: UNNEST (and other functions) WITH ORDINALITY
Date: 2013-01-23 06:35:52
Message-ID: 20130123063552.GD26369@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: David Fetter <david(at)fetter(dot)org>
Cc: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: UNNEST (and other functions) WITH ORDINALITY
Date: 2013-01-23 18:12:37
Message-ID: 20130123181237.GE4249@alvh.no-ip.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

From: David Fetter <david(at)fetter(dot)org>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: UNNEST (and other functions) WITH ORDINALITY
Date: 2013-01-23 18:15:27
Message-ID: 20130123181527.GB2432@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: David Fetter <david(at)fetter(dot)org>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: UNNEST (and other functions) WITH ORDINALITY
Date: 2013-01-23 19:40:45
Message-ID: 20130123194045.GF23670@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

From: David Fetter <david(at)fetter(dot)org>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: UNNEST (and other functions) WITH ORDINALITY
Date: 2013-01-23 19:46:27
Message-ID: 20130123194627.GD2432@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

From: David Fetter <david(at)fetter(dot)org>
To: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: UNNEST (and other functions) WITH ORDINALITY
Date: 2013-02-08 21:12:15
Message-ID: 20130208211215.GO2054@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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 Content-Type Size
ordinality_20130208_1242.patch text/plain 85.4 KB
From: David Fetter <david(at)fetter(dot)org>
To: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Patch: UNNEST (and other functions) WITH ORDINALITY
Date: 2013-06-17 05:33:44
Message-ID: 20130617053344.GA19441@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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 Content-Type Size
ordinality_07.diff text/plain 85.4 KB