Mastering PostgreSQL 9.6

Using lateral joins

In SQL, a lateral join can basically be seen as some sort of loop. It basically allows us to parameterize a join and execute everything inside the LATERAL clause more than once. Here is a simple example:

test=# SELECT *     FROM generate_series(1, 4) AS x,           LATERAL (SELECT array_agg(y)                    FROM generate_series(1, x) AS y                  ) AS z;  x | array_agg  ---+-----------  1 | {1}  2 | {1,2}  3 | {1,2,3}  4 | {1,2,3,4} (4 rows) 

The LATERAL clause will be called for each x. To the end user, it is basically some sort of loop.