2

I was just reading this answer by Erwin Brandstetter. Informative as usual, it makes note that (multi-parameter) unnest is only allowed in the FROM clause. I had never tried it in the SELECT clause so I'm not that shocked, but I never saw function that could control for execution context: FROM vs SELECT set-returning-functions.

Docs for unnest(anyarray, anyarray [, ...]) (the version with multiple parameters)

expand multiple arrays (possibly of different types) to a set of rows. This is only allowed in the FROM clause; see Section 7.2.1.4

How does one declare their set returning function to be FROM-clause only, or is this only permissible with unnest?

  • 1
    You might want to make clear that the restriction only applies to unnest() with multiple parameters. – Erwin Brandstetter Jan 8 '17 at 4:26
4

I am afraid that's only possible with unnest() after all.

unnest() with multiple parameters is a special Postgres feature, that's internally rewritten into multiple unnest() calls that are combined more reasonably than parallel unnest() in the SELECT list would be, i.e. in a sane way if the number of elements does not match.

The behavior was finally sanitized with Postgres 10:

See:

EXPLAIN (VERBOSE) SELECT *
FROM   unnest('{a,b}'::text[], '{1,2}'::text[]);
Function Scan on unnest  (cost=0.01..1.00 rows=100 width=64)
  Output: unnest, unnest_1
  Function Call: unnest('{a,b}'::text[]), unnest('{1,2}'::text[])

It's a special use case for the ROWS FROM construct introduced with the same Postgres version 9.4. The manual:

The special table function UNNEST may be called with any number of array parameters, and it returns a corresponding number of columns, as if UNNEST (Section 9.18) had been called on each parameter separately and combined using the ROWS FROM construct.

Bold emphasis mine.

If you try unnest() with multiple parameters in the SELECT list, you get:

ERROR: function unnest(text[], text[]) does not exist

In fact, there is no unnest() function with multiple parameters registered in the system:

SELECT proname, proargtypes, proargtypes[0]::regtype
FROM   pg_proc
WHERE  proname = 'unnest';
 proname | proargtypes | proargtypes
---------+-------------+-------------
 unnest  | 2277        | anyarray
 unnest  | 3614        | tsvector

That would be confusing if you were not aware of the first part of my answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.