Sql Multiple Unnest In Single Select List

I was implementing a Query system. I implemented unnest function. Now user was asking about using multiple unnest in a single select statement. I was using PostgreSQL as kind of gu

Solution 1:

This isn't about unnest as such, but about PostgreSQL's very weird handling of multiple set-returning functions in the SELECT list. Set-returning functions in SELECT aren't part of the ANSI SQL standard.

You will find behaviour much saner with LATERAL queries, which should be preferred over using a a set-returning function in FROM as much as possible:

select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;

e.g.

regress=> select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;
 a | b 
---+---
 1 | 1
 1 | 2
 1 | 3
 2 | 1
 2 | 2
 2 | 3
(6 rows)

The only time I still use multiple set-returning functions in SELECT is when I want to pair up values from functions that both return the same number of rows. The need for that will go away in 9.4, with multi-argument unnest and with support for WITH ORDINALITY.