CODEWITHSUNDEEP
postgresql
Andy Carlson
Andy Carlson

Reputation: 3909

Why are "subqueries used as an expression" allowed to return a SETOF something but not a TABLE?

So I have a table with some arrays of integers.

CREATE TABLE arrays (
  nums int[],
  id serial PRIMARY KEY
);

INSERT INTO arrays VALUES
  ('{1, 5, 72}', 1),
  ('{3}', 2),
  ('{32, 6}', 3);

If I want to spread those array elements to separate rows, I can do it with a function that returns a SETOF INT, such as unnest.

SELECT
  id,
  unnest(nums)
FROM arrays;

-- id   unnest
-- 1    1
-- 1    5
-- 1    72
-- 2    3
-- 3    32
-- 3    6

But I always thought that subqueries inside a SELECT clause had to return a single value! Yet unnest is returning multiple values. Postgres just handles it by duplicating the row for each value. But if I try another subquery that returns multiple values, like this:

SELECT
  a.id,
  (SELECT a.nums[1] UNION SELECT a.nums[2])
FROM arrays a;

-- ERROR: more than one row returned by a subquery used as an expression

That gives an error. It seems like it should be handled in an analogous manner to unnest. Why is unnest allowed to return multiple values but not this?

Upvotes: 3

Views: 144

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

That's a PostgreSQL wart^H^H^H^Hfeature.

It is actually documented:

Functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the set-returning function is invoked, and an output row is generated for each element of the function's result set.

[...]

PostgreSQL's behavior for a set-returning function in a query's select list is almost exactly the same as if the set-returning function had been written in a LATERAL FROM-clause item instead.

Note that the behavior when there are several set-returning functions in the SELECT list has changed in v10; see the release notes.

I recommend that you don't use that and put such functions in the FROM list where they belong.

Upvotes: 4

Related Questions