2010/11/14 berelith <nicolasbeuzeboc@gmail.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
SELECT80,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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>