Re: returning more than one value from a function

On Friday 30. April 2010 18.37.34 Lonni J Friedman wrote:
> Greetings,
> I'm attempting to create a PL/PGSQL function with an IF/THEN
> conditional. Basically, all I really need is the ability to run a
> long/complex SQL query based on the value of the newest row of one
> column in a specific table (if its equal to 1 then run the SQL, if
> anything else don't run it). In pseudo code, something like:
>
> CREATE OR REPLACE FUNCTION foo0 RETURN text AS $$
> IF (SELECT current_status from table0 WHERE id in (SELECT max(id) FROM
> table0))='1' THEN
> <LONG SQL QUERY>
> END IF;
> LANGUAGE 'plpgsql' ;
>
>
> This seems like a fairly simple requirement, yet I can't find any way
> to do this without creating a function. The problem is that the
> long/complex SQL query needs to return multiple columns of output (not
> just a single value), and functions can only return a single
> value/column, so I'm a bit stuck on how to make this work.
>
> Is there an alternative solution that I'm missing?

Don't know if you're aware of the CASE WHEN ... ELSE ... END construct, which
is vanilla SQL, and is very similar to the usual IF ... THEN ... ELSE.
<http://www.postgresql.org/docs/current/static/functions-
conditional.html#AEN15201>

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/