Re: CASE statement and SETOF values

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;