As part of my Bachelor's Thesis for the Database Research Group at the University of Tübingen, I have implemented the WITH ORDINALITY feature for the core table functions ¹ :

• unnest
• repeat
• repeat_row
• generate_series
• range
• glob
• read_csv
• read_csv_auto

In short, this feature optionally adds a one-based "index"-column, called Ordinality², to the output of all of table functions listed above, e.g.:

SELECT * FROM range(5) WITH ORDINALITY;

produces

┌───────┬────────────┐
│ range │ Ordinality │
│ int64 │   int32    │
├───────┼────────────┤
│     0 │          1 │
│     1 │          2 │
│     2 │          3 │
│     3 │          4 │
│     4 │          5 │
└───────┴────────────┘

This can be used to create or maintain an explicit order of elements and can, for example, find its uses when reading from files. When there are multiple possible behaviours, I made sure to mimic the behaviour implemented in Postgres, more specifically:

• When a function consumes rows from more than one source, WITH ORDINALITY yields seperate indices for each source, e.g., a UNION of multiple range-calls:

  SELECT u, Ordinality
  FROM   (SELECT range(5) UNION SELECT range(5,10)) AS _(l),
         unnest(l) WITH ORDINALITY AS __(u)
  ORDER BY l,u;

  produces

  ┌───────┬────────────┐
  │   u   │ Ordinality │
  │ int64 │   int32    │
  ├───────┼────────────┤
  │     0 │          1 │
  │     1 │          2 │
  │     2 │          3 │
  │     3 │          4 │
  │     4 │          5 │
  │     5 │          1 │  <-- index reset!
  │     6 │          2 │
  │     7 │          3 │
  │     8 │          4 │
  │     9 │          5 │
  ├───────┴────────────┤
  │ 10 rows  2 columns │
  └────────────────────┘
  

• When read_csv reads from multiple .csv files, WITH ORDINALITY considers all .csv files to be the same row source, i.e., the counter doesn't reset at the start of each file.