One of the neat little features that arrived at PostgreSQL 9.4 is the WITH ORDINALITY ANSI-SQL construct. What this construct does is to tack an additional column called ordinality as an additional column when you use a set returning function in the FROM part of an SQL Statement.
Here is the simple example of its use:
SELECT *
FROM unnest('{my,dog, eats, dog food}'::text[] )
WITH ordinality;
unnest | ordinality ----------+------------ my | 1 dog | 2 eats | 3 dog food | 4
If you aren't happy with the default column names, you can change them by revising your query like this
SELECT f.*
FROM unnest('{my,dog, eats, dog food}'::text[] )
WITH ordinality As f(phrase, sort_order);
Which would output this:
phrase | sort_order ----------+------------ my | 1 dog | 2 eats | 3 dog food | 4
The greatest value of WITH ORDINALITY comes when you apply it to rows of data. How do you do that when you need to use WITH ORDINALITY. This is where one of our favorite constructs, the LATERAL construct comes to the rescue.
First let's construct our table with a text array column for demonstration. Note that the fish has no tags.
CREATE TABLE pets(pet varchar(100) PRIMARY KEY, tags text[]);
INSERT INTO pets(pet, tags)
VALUES ('dog', '{big, furry, friendly, eats steak}'::text[]),
('cat', '{small, snob, eats greenbeans, plays with mouse}'::text[]),
('mouse', '{very small, fits in pocket, eat peanuts, watches cat}'::text[]),
('fish', NULL);
If you do a cross join, you'll leave out fish because he's got no tags
SELECT pet, sort_order, tag
FROM pets, unnest(tags)
WITH ORDINALITY As f(tag, sort_order) ;pet | sort_order | tag ------+------------+------------------ dog | 1 | big dog | 2 | furry dog | 3 | friendly dog | 4 | eats steak cat | 1 | small cat | 2 | snob cat | 3 | eats greenbeans cat | 4 | plays with mouse mouse | 1 | very small mouse | 2 | fits in pocket mouse | 3 | eat peanuts mouse | 4 | watches cat
In order to include pets that have no tags, you need to do a LEFT JOIN like so
SELECT pet, sort_order, tag
FROM pets LEFT JOIN
LATERAL unnest(tags)
WITH ORDINALITY As f(tag, sort_order) ON true;
And voila, all our pets are accounted for:
pet | sort_order | tag ------+------------+------------------ dog | 1 | big dog | 2 | furry dog | 3 | friendly dog | 4 | eats steak cat | 1 | small cat | 2 | snob cat | 3 | eats greenbeans cat | 4 | plays with mouse mouse | 1 | very small mouse | 2 | fits in pocket mouse | 3 | eat peanuts mouse | 4 | watches cat fish | |