Only time I've ever really used CROSS LATERAL JOIN in postgres is when working with JSONB documents that I'd like to put into a relational schema, e.g. given the data
CREATE TABLE my_documents(doc JSONB); SELECT t.id,t.value FROM my_documents CROSS JOIN LATERAL jsonb_to_recordset(doc #> '{data}') AS t(id INTEGER, value TEXT);
... should output the following table:
id | value
----+-------
1 | foo
2 | bar
Useful for manipulating JSON in the database instead of marshalling and unmarshalling everything in the application layer.
IIRC it's really only in a LATERAL JOIN because laterals are the only production rules that let you alias a function call (jsonb_to_recordset()) with explicitly declared column types.
> However, it working as a lateral join is critical as you need the function to fire for every row.
Right, what I mean is, for functions in the standard expression position, e.g. `SELECT COALESCE(..., 0)`, those functions will also fire for every row. jsonb_to_recordset however needs to know the schema of the table it will output, and the only way syntactically to declare the column types output by a function returning a recordset is in LATERAL clauses [0].
IIRC it's really only in a LATERAL JOIN because laterals are the only production rules that let you alias a function call (jsonb_to_recordset()) with explicitly declared column types.