Geek Answers Handbook

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

If I try to create a column whose value is a selection that returns 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, I get the behavior that 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 is the difference?

+6
function sql postgresql subquery
source share
2 answers

While OMG Ponies answer is completely correct, I would rather say the following: you confuse SELECT f() with SELECT literal .

  • SELECT f() executes the function and returns its result. And the table return function can also be written as SELECT * FROM f() - which is even more elegant. Since Pg does not yet store the procedures — less scheduling can be done through functions — we use SELECT because Microsoft SQL uses EXECUTE

  • SELECT literal is a literal return method (something that can fit into a row / column).

+4
source share

This is not a comparison of apples with apples.

 select * FROM (select 1 union ALL select 2)

... equivalent to your function.

A column in a SELECT clause can only return one value for each record. This is not possible with your UNION example. So I converted it to a view / inline view, which is what happens with the function example.

+5
source share

More articles:


All Articles