Re: Need help with plpgsql function.

2010/11/14 berelith <nicolasbeuzeboc(at)gmail(dot)com>:
>
> Hi,
>
> I'm creating the function on a postgres 8.2 server.
> I would like the function to accept half a dozen varied parameters (varchars
> and timestamps).
> The first parameter will determine which one of the 6 different select
> queries that function is going to run.
>
> The function will return all the rows from the chosen select statement.
>
> I've been reading the postgresql documentation in creating functions that
> returns rowset and I've read about  plpgsql. It seems to be what I need
> because there is going to be conditional statements.
>
> I'm just having a hard time putting it all together, and I'm not sure about
> the syntax and how to return the selected rows back into OUT parameters.
>
> This is a short pseudo example:
>
> CREATE OR REPLACE FUNCTION report (
> -- function arguments, type will determine which one of the 6 queries to run
>  IN type       character varying(20),
>  IN param1   character varying(255),
>  IN param2   timestamp,
> -- returned values
>  OUT code          table.code%TYPE,
>  OUT name         table.name%TYPE
> )
> RETURNS SETOF rows
> { LANGUAGE PLPGSQL
> IF type like 'type A' THEN
>    SELECT code, name INTO rows FROM tableA join some table ....;
>    return rows
> ELSIF type like 'type B' THEN
>    SELECT code, name INTO rows FROM tableB join someothertable ... ;
>    return rows
> ELSE
>    RETURN VOID
> END IF;
> }
>
>

Hello

you can use a RETURN QUERY statement - some like

CREATE OR REPLACE FUNCTION foo(IN i int, OUT a int, OUT b int)
RETURNS SETOF RECORD AS $$
BEGIN
IF i = 1 THEN
RETURN QUERY SELECT 10,20 UNION ALL SELECT 30,40;
ELSE
RETURN QUERY SELECT 60,70 UNION ALL SELECT 80,90;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM foo(1);
SELECT * FROM foo(2);

Regards

Pavel Stehule

>
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-with-plpgsql-function-tp3264047p3264047.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>