On Mon, 24 Jul 2006, Christian Schoenebeck wrote:
> Consider the following server side function:
>
> CREATE FUNCTION my_function(int4) RETURNS SETOF int8 AS
> $BODY$
> SELECT
> CASE WHEN (some_condition)
> THEN (
> SELECT ... -- arbitrary select (returning row(s) of int8 values)
> )
> ELSE (
> SELECT ... -- arbitrary select (returning row(s) of int8 values)
> )
> END
> $BODY$
> LANGUAGE 'sql' VOLATILE;
>
> This function works fine if one of the two inner SELECT statements returns
> exactly one result (one row), but fails whenever one of them returns more
> than one result / rows.
>
> What is the reason? I mean the function is declared as returning "SETOF int8",
> so why does it expect a scalar?
The above basically looks like:
CASE WHEN <search condition> THEN <value expression> ELSE
<value expression> END.
In SQL92 at least, the form of <value expression> which looks like (SELECT
...) is <scalar subquery> which is limited to 1 column and 1 row. The
other subquery forms don't look legal in that position unless they changed
that in a later version of the spec.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
