From: berelith <nicolasbeuzeboc@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Need help with plpgsql function.
Date: Sat, 13 Nov 2010 15:48:02 -0800 (PST)
Message-ID: <1289692082542-3264047.post@n5.nabble.com> (raw)
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;
}
--
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nicolasbeuzeboc@gmail.com
Subject: Re: Need help with plpgsql function.
In-Reply-To: <1289692082542-3264047.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox