Howto return values from a function

I'm still trying to learn to write plpgsql functions, but I find the
docs a little short on examples on how to return stuff from a
function. I'm very grateful for any help on this.

There are some basic cases I've identified.

1) when I want to return all records found by a query , like this
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
RETURN QUERY SELECT a,b,c,d,... FROM T1,T2,... WHERE ....;
END;

but what do I write instead of ????

2) when I select stuff, iterate over the result before returning it
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
FOR result IN SELECT .....
LOOP
do something with result...
RETURN NEXT result
END LOOP;
END

I'm nto sure here, but It seems to mee that there are two other ways
of doing case 2.
2a) run the query once more and RETURN QUERY at the end instead of the
RETURN NEXT statement.
2b) store the result in some temporary storage... (I'm not sure how,
it's just a feeling I get that this should be possible, I might be
completely wrong) and then return the whole result a once.
As usual, what do I write instead of ????

3) In the third case, I want to create the values I return by joining
many values. Something like this
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
myvar := .....
myvar2 := ....
FOR result IN SELECT ...
LOOP
FOR result2 IN SELECT .....
LOOP
RETURN NEXT ????????????;
END LOOP;
END LOOP;
RETURN
END

The ?????????????? part should perhaps be something like rows with the values
[ myvar, myvar2, result.f1, result.f2, result2.f5, result2.f7 ]

On Thu, May 15, 2008 at 02:25:36PM +0200, A B wrote:
> I'm still trying to learn to write plpgsql functions, but I find the
> docs a little short on examples on how to return stuff from a
> function. I'm very grateful for any help on this.

What exactly about the documentation isn't clear?

http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

> CREATE FUNCTION foo() RETURNS ???? AS

> but what do I write instead of ????

Like the documentation says: SETOF sometype.

> I'm nto sure here, but It seems to mee that there are two other ways
> of doing case 2.

How you generate the results is up to you. when you have them you
either use RETURN NEXT or RETURN QUERY to return them to the caller.

> 3) In the third case, I want to create the values I return by joining
> many values. Something like this
> CREATE FUNCTION foo() RETURNS ???? AS

You can always use out parameters if you feel better about it:

CREATE FUNCTION foo(col1 int4 OUT, col2 text OUT, ...) AS ...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

> What exactly about the documentation isn't clear?
I would have liked a few more examples... but that is perhaps just me.

> Like the documentation says: SETOF sometype.
Ah, so I just create my own type with "CREATE TYPE ..." and use that
type in the function.

am Thu, dem 15.05.2008, um 14:46:02 +0200 mailte A B folgendes:
> > What exactly about the documentation isn't clear?
> I would have liked a few more examples... but that is perhaps just me.

http://www.java2s.com/Code/PostgreSQL/CatalogPostgreSQL.htm

>
> > Like the documentation says: SETOF sometype.
> Ah, so I just create my own type with "CREATE TYPE ..." and use that
> type in the function.

One solution, there are much more.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

> How you generate the results is up to you. when you have them you
> either use RETURN NEXT or RETURN QUERY to return them to the caller.

Now I get the reply

ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "actionlist" line 11 at return next

and here is the function (and a datatype that is used for the return values)

CREATE TYPE Ttelnr_action AS (
nr VARCHAR(30),
action CHAR(1)
);

CREATE OR REPLACE FUNCTION actionlist(tid_ TIMESTAMP) RETURNS SETOF
Ttelnr_action AS $$
DECLARE
rec RECORD;
result Ttelnr_action;
BEGIN
FOR rec IN SELECT DISTINCT custid,nr,action FROM Actions
LOOP
IF rec.action = 'view_important_message' THEN
result.nr := rec.nr;
result.action := 'd';
RETURN NEXT result;
ELSIF rec.action = 'download_movie' THEN
result.nr := rec.nr;
result.action := 'v';
RETURN NEXT result;
END IF;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

2008/5/16 A B <gentosaker(at)gmail(dot)com>:
>> How you generate the results is up to you. when you have them you
>> either use RETURN NEXT or RETURN QUERY to return them to the caller.
>
> Now I get the reply
>
> ERROR: set-valued function called in context that cannot accept a set
> CONTEXT: PL/pgSQL function "actionlist" line 11 at return next

You probably need to do

SELECT * FROM actionlist(123)

Ian Barwick

Hello

use SELECT * FROM actionlist(...);

Regards
Pavel Stehule

2008/5/16 A B <gentosaker(at)gmail(dot)com>:
>> How you generate the results is up to you. when you have them you
>> either use RETURN NEXT or RETURN QUERY to return them to the caller.
>
> Now I get the reply
>
> ERROR: set-valued function called in context that cannot accept a set
> CONTEXT: PL/pgSQL function "actionlist" line 11 at return next
>
> and here is the function (and a datatype that is used for the return values)
>
>
> CREATE TYPE Ttelnr_action AS (
> nr VARCHAR(30),
> action CHAR(1)
> );
>
> CREATE OR REPLACE FUNCTION actionlist(tid_ TIMESTAMP) RETURNS SETOF
> Ttelnr_action AS $$
> DECLARE
> rec RECORD;
> result Ttelnr_action;
> BEGIN
> FOR rec IN SELECT DISTINCT custid,nr,action FROM Actions
> LOOP
> IF rec.action = 'view_important_message' THEN
> result.nr := rec.nr;
> result.action := 'd';
> RETURN NEXT result;
> ELSIF rec.action = 'download_movie' THEN
> result.nr := rec.nr;
> result.action := 'v';
> RETURN NEXT result;
> END IF;
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

A B wrote:
>> How you generate the results is up to you. when you have them you
>> either use RETURN NEXT or RETURN QUERY to return them to the caller.
>
> Now I get the reply
>
> ERROR: set-valued function called in context that cannot accept a set
> CONTEXT: PL/pgSQL function "actionlist" line 11 at return next

It's a source of rows, so you need to treat it like a table or a view:

SELECT * FROM actionlist(...);

--
Richard Huxton
Archonet Ltd

Great! :D That did the trick!
Thank you so very much!

2008/5/16 Richard Huxton <dev(at)archonet(dot)com>:
> A B wrote:
>>>
>>> How you generate the results is up to you. when you have them you
>>> either use RETURN NEXT or RETURN QUERY to return them to the caller.
>>
>> Now I get the reply
>>
>> ERROR: set-valued function called in context that cannot accept a set
>> CONTEXT: PL/pgSQL function "actionlist" line 11 at return next
>
> It's a source of rows, so you need to treat it like a table or a view:
>
> SELECT * FROM actionlist(...);
>
>
> --
> Richard Huxton
> Archonet Ltd
>