Re: CASE statement and SETOF values

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.