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.
phrase | sort_order
my | 1
dog | 2
eats | 3
dog food | 4
LATERAL WITH ORDINALITY
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.