WITH ORDINALITY adds a numbering column to table function output. For example, you can number list items when unnesting lists:
CREATE TABLE quizzes(scores int list);
INSERT INTO quizzes VALUES (LIST[5, 7, 8]), (LIST[3, 3]);
SELECT scores, score, ordinality
FROM
quizzes,
unnest(scores) WITH ORDINALITY AS t(score, ordinality);
scores | score | ordinality
---------+-------+------------
{3,3} | 3 | 1
{3,3} | 3 | 2
{5,7,8} | 5 | 1
{5,7,8} | 7 | 2
{5,7,8} | 8 | 3
(5 rows)You can use the ROWS FROM clause to zip the outputs of multiple table functions rather than taking their cross product:
SELECT *
FROM
ROWS FROM (
generate_series(1, 2),
generate_series(6, 7)
) AS t(g1, g2);
g1 | g2
----+----
1 | 6
2 | 7
(2 rows)We have rewritten how we implement the WITH ORDINALITY and ROWS FROM clauses, and thus fixed a performance issue and an incorrect ordering bug.