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?
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.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With