How do you declare a set-returning-function to only be allowed in the FROM clause?

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?

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:

• What is the expected behaviour for multiple set-returning functions in select clause?

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.

StackExchange Database Administrators Q#160309, answer score: 4