PostgreSQL: Why can't subqueries as expressions return more than one row, but functions can?

Question

If I try to create a column whose value is a select returning more than one row, I get an error.

=> select (select 1 union select 2);
ERROR:  more than one row returned by a subquery used as an expression

But if I create a function that does the same thing, I get the behavior I want.

=> create or replace function onetwo() returns setof integer as $$
$> select 1 union select 2 
$> $$ language 'sql' strict immutable;
CREATE FUNCTION
=> select onetwo();
 onetwo 
--------
      1
      2

Why the difference?

Answer 1

It's not an apples to apples comparison.

select * 
  FROM (select 1 
        union ALL 
        select 2)

...is equivalent to your function.

A column in the SELECT clause can only return a single value per record. Which is impossible with your UNION example. So I converted it into a derived table/inline view, which is what is happening with the function example.

Answer 2

While OMG Ponies answer is entirely correct I'd rather put it like this: You're confusing SELECT f() with SELECT literal.

• SELECT f() executes a function and returns its result. And, a table returning function can also be written as SELECT * FROM f() -- which is even more elegant. Because Pg doesn't yet have stored procedures -- less scheduling they can be done through functions -- we use SELECT as Microsoft SQL uses EXECUTE

• SELECT LITERAL is a method of returning a literal (something that can fit in a row/column).