Обсуждение: CASE statement and SETOF values

HI!

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?

CU
Christian

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.

Am Dienstag, 25. Juli 2006 00:01 schrieb Stephan Szabo:
> 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.

Ok, and is there any way to circumvent this problem?

CU
Christian

On Tue, 25 Jul 2006, Christian Schoenebeck wrote:

> Am Dienstag, 25. Juli 2006 00:01 schrieb Stephan Szabo:
> > 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.
>
> Ok, and is there any way to circumvent this problem?

Well, the easiest one is to use a procedural language to get conditional
statements. For example, something like the following (untested) plpgsql
body:

DECLARE
 r record
BEGIN
 IF (some_condition) THEN
  FOR r IN SELECT ... LOOP
   RETURN NEXT r;
  END LOOP;
 ELSE
  FOR r IN SELECT ... LOOP
   RETURN NEXT r;
  END LOOP;
 END IF;
 RETURN;
END;

Am Dienstag, 25. Juli 2006 17:56 schrieben Sie:
> You could use a procedural language like plpgsql.

Ok, using the plpgsql approach I tried this:

CREATE FUNCTION my_function(int4) RETURNS SETOF int8 AS
$BODY$
DECLARE
    myrow RECORD;
BEGIN
    IF (some_condition) THEN
            FOR myrow IN SELECT ... -- some select statement
            LOOP
                RETURN NEXT myrow."foocolumn";
            END LOOP;
        ELSE
            FOR myrow IN SELECT ... -- some select statement
            LOOP
                RETURN NEXT myrow."foocolumn";
            END LOOP;
    END IF;
    RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

But creating this function fails, because it's "missing a LOOP" statement.
What am I missing?

CU
Christian