Waiting for 9.4 – Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)

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.

10 thoughts on “Waiting for 9.4 – Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)”

  1. 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.

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

  3. 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.

  4. @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)

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

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

  9. So it looks like the JSON stuff is not supported. E.g., the following fails.

    select json_array_elements_text(‘[1,2,3,4]’::json) with ordinality;

  10. The problem is that you can’t use WITH ORDINALITY in the select list, just the FROM clause. This works fine:

    # select * from json_array_elements_text(‘[1,2,3,4]’::json) with ordinality;
    value | ordinality
    ——-+————
    1 | 1
    2 | 2
    3 | 3
    4 | 4
    (4 rows)

Comments are closed.