On 29th of July, Greg Stark committed patch:

Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)
 
Author: Andrew Gierth, David Fetter
Reviewers: Dean Rasheed, Jeevan Chalke, Stephen Frost

This is great stuff. Basically it acts like “row_number()" for functions that return set of records, but without need to add window functions, and potential subselects/srfs.

How does it work? That's simple:

$ select * from unnest('{h,d,e,q}'::text[]) with ordinality;
 unnest | ordinality 
--------+------------
 h      |          1
 d      |          2
 e      |          3
 q      |          4
(4 rows)

Basically, “with ordinality" adds another column, named “ordinality" which contains row number.

It can of course be renamed, using standard “as" syntax:

$ select id, val from unnest('{h,d,e,q}'::text[]) with ordinality as t(val, id);
 id | val 
----+-----
  1 | h
  2 | d
  3 | e
  4 | q
(4 rows)

What's more – every function that returns set of records can have ordinality now too:

$ select * from regexp_matches('depesz', '(.)(.)', 'g') with ordinality;
 regexp_matches | ordinality 
----------------+------------
 {d,e}          |          1
 {p,e}          |          2
 {s,z}          |          3
(3 rows)
 
$ select * from ts_debug('this is some sample text') with ordinality;
   alias   |   description   | token  |  dictionaries  |  dictionary  | lexemes | ordinality 
-----------+-----------------+--------+----------------+--------------+---------+------------
 asciiword | Word, all ASCII | this   | {english_stem} | english_stem | {}      |          1
 blank     | Space symbols   |        | {}             | [null]       | [null]  |          2
 asciiword | Word, all ASCII | is     | {english_stem} | english_stem | {}      |          3
 blank     | Space symbols   |        | {}             | [null]       | [null]  |          4
 asciiword | Word, all ASCII | some   | {english_stem} | english_stem | {}      |          5
 blank     | Space symbols   |        | {}             | [null]       | [null]  |          6
 asciiword | Word, all ASCII | sample | {english_stem} | english_stem | {sampl} |          7
 blank     | Space symbols   |        | {}             | [null]       | [null]  |          8
 asciiword | Word, all ASCII | text   | {english_stem} | english_stem | {text}  |          9
(9 rows)

The concept is very simple, and I think usage is obvious, but it was definitely missing from Pg.

Great stuff, thanks guys.

  1. 8 comments

  2. Jul 30, 2013

    I assume these won’t always return in order? because set’s are unordered, and that if you had a sequence the numbers from ordinality could differ from the numbers inserted by the sequence.

  3. Jul 30, 2013

    @Caleb:
    sets are unordered, but are always returned/processed in *some* order. And the ordinality reflects of order of rows returned from SRF.

  4. # greg
    Jul 30, 2013

    The ordering is the critical point actually. Set returning functions return their records in a specific order. Unnest returns them in the order of the array which is an ordered list.

    But once you use it in SQL tables are normally unordered so you have no way to make use of the ordering.

    In particular you CANNOT use row_number() to emulate WITH ORDINALITY. row_number() requires an ORDER BY clause and there’s no column to order by to produce the implicit order from the function.

  5. Jul 30, 2013

    @Greg:
    well, not really:

    $ select row_number() over (), * from unnest('{a,b,c,d}'::text[]);
     row_number | unnest 
    ------------+--------
              1 | a
              2 | b
              3 | c
              4 | d
    (4 rows)
  6. # greg
    Jul 30, 2013

    Huh. The standard requires the order by but that’s interesting. It does make the feature a lot less compelling

  7. # WR
    Jul 30, 2013

    Wow, what a nice feature!
    With each PG version complex queries become more clear and easier to read.

  8. # xocolatl
    Jul 30, 2013

    “row_number() over ()” is undefined.

  9. Aug 3, 2013

    Nice! Looks like I can finally get rid of the generate_subscripts() hack I’ve been using.

Leave a comment