sql: support unnest() built-in function

[cuongdo]

https://www.postgresql.org/docs/9.6/static/functions-array.html

This is needed for the second line of the following ActiveRecord query:

WITH pk_constraint AS (
  SELECT conrelid, unnest(conkey) AS connum FROM pg_constraint
  WHERE contype = 'p'
    AND conrelid = '"numbers"'::regclass
), cons AS (
  SELECT conrelid, connum, row_number() OVER() AS rownum FROM pk_constraint
)
SELECT attr.attname FROM pg_attribute attr
INNER JOIN cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.connum
ORDER BY cons.rownum

[jordanlewis]

Supporting this function requires the addition of an interesting implicit cross-join behavior. It looks like using any function that returns a set of rows (proretset is set to true in pg_proc) in the render list of a SELECT causes postgres to automatically cross join its results with the results of all of the other normal renders. Check out these examples:

jordan=# select unnest(array[1,2]), generate_series(3,4), x FROM (VALUES (5), (6)) as t(x);
 unnest | generate_series | x
--------+-----------------+---
      1 |               3 | 5
      2 |               4 | 5
      1 |               3 | 6
      2 |               4 | 6

As you can see, the results of unnest and generate_series get concatenated into two, two-column rows - one for each of the output rows of each function. The final result is the cartesian product of these two rows and the two single-column rows produced by the VALUES expression.

The concatenation behavior of the two retset functions seems to only occur when they return the same number of rows, however. In the following example, the two functions return 1 and 2 rows respectively, and their results get cross-producted as well.

jordan=# select unnest(array[1]), generate_series(2,3);
 unnest | generate_series
--------+-----------------
      1 |               2
      1 |               3
(2 rows)

[jordanlewis]

Ah, this behavior is discussed in #11215, which is a prerequisite of this issue.

[eldos-dl]

So are these kind of queries supposed to work or will work in future ?

SELECT unnest(c.conkey) AS colid FROM pg_catalog.pg_constraint AS c;

SELECT unnest(c.conkey) AS colid, generate_series(1, array_length(c.conkey, 1)) AS arridx
FROM pg_catalog.pg_constraint AS c;