pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help with plpgsql function.
4+ messages / 3 participants
[nested] [flat]
* Need help with plpgsql function.
@ 2010-11-13 23:48 berelith <nicolasbeuzeboc@gmail.com>
2010-11-14 07:15 ` Re: Need help with plpgsql function. Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: berelith @ 2010-11-13 23:48 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Need help with plpgsql function.
2010-11-13 23:48 Need help with plpgsql function. berelith <nicolasbeuzeboc@gmail.com>
@ 2010-11-14 07:15 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-11-14 18:37 ` Re: Need help with plpgsql function. Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Pavel Stehule @ 2010-11-14 07:15 UTC (permalink / raw)
To: berelith <nicolasbeuzeboc@gmail.com>; +Cc: pgsql-sql
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 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Need help with plpgsql function.
2010-11-13 23:48 Need help with plpgsql function. berelith <nicolasbeuzeboc@gmail.com>
2010-11-14 07:15 ` Re: Need help with plpgsql function. Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-11-14 18:37 ` Adrian Klaver <adrian.klaver@gmail.com>
2010-11-14 18:41 ` Re: Need help with plpgsql function. Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2010-11-14 18:37 UTC (permalink / raw)
To: pgsql-sql; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; berelith <nicolasbeuzeboc@gmail.com>
On Saturday 13 November 2010 11:15:51 pm Pavel Stehule wrote:
> > }
>
> 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
>
FYI the OP is using 8.2 :) RETURN QUERY is 8.3+
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Need help with plpgsql function.
2010-11-13 23:48 Need help with plpgsql function. berelith <nicolasbeuzeboc@gmail.com>
2010-11-14 07:15 ` Re: Need help with plpgsql function. Pavel Stehule <pavel.stehule@gmail.com>
2010-11-14 18:37 ` Re: Need help with plpgsql function. Adrian Klaver <adrian.klaver@gmail.com>
@ 2010-11-14 18:41 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2010-11-14 18:41 UTC (permalink / raw)
To: adrian.klaver@gmail.com; +Cc: pgsql-sql@postgresql.org, berelith <nicolasbeuzeboc@gmail.com>
2010/11/14 Adrian Klaver <adrian.klaver@gmail.com>:
> On Saturday 13 November 2010 11:15:51 pm Pavel Stehule wrote:
>
>> > }
>>
>> 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
>>
>
> FYI the OP is using 8.2 :) RETURN QUERY is 8.3+
sorry :)
then
RETURN QUERY query -->
DECLARE r record;
BEGIN
FOR r IN SELECT ....
RETURN NEXT r;
END FOR;
...
Regards
Pavel Stehule
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-11-14 18:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-13 23:48 Need help with plpgsql function. berelith <nicolasbeuzeboc@gmail.com>
2010-11-14 07:15 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-11-14 18:37 ` Adrian Klaver <adrian.klaver@gmail.com>
2010-11-14 18:41 ` Pavel Stehule <pavel.stehule@gmail.com>
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