CASE statement and SETOF values

From: Christian Schoenebeck <cschoene(at)stud(dot)hs-heilbronn(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: CASE statement and SETOF values
Date: 2006-07-24 21:27:57
Message-ID: 200607242327.57474.cschoene@stud.hs-heilbronn.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Roderick A. Anderson 2006-07-24 21:36:11 Does a GRANT on a table cascade/implied to its SEQUENCES
Previous Message Alexandre Arruda 2006-07-24 20:57:14 Re: pg_dump & pg_restore suggestion