Thread: help..postgresql mulyiple return values

help..postgresql mulyiple return values

From
Michael Joseph Tan
Date:
hi,
im new in postgresql, generally new in databases.
 
im trying to make a function using PGAdminIII which returns several types,
example, my query is:
 
"select count(id) as requests, organization from connection_requests group by organization"
 
id is of type int8, organization is of type varchar(50).
 
basically the query would return coun(id), and a varchar(50) which is organization.
 
i really dont know what to put on the return type.
 
what would be the best solution?
 
hope to hear from you guys asap..
 
thank you


Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.

Re: help..postgresql mulyiple return values

From
"Tony Wasson"
Date:
On 5/12/06, Michael Joseph Tan <josephtan2k@yahoo.com> wrote:
>
> hi,
> im new in postgresql, generally new in databases.
>
> im trying to make a function using PGAdminIII which returns several types,
> example, my query is:
>
> "select count(id) as requests, organization from connection_requests group
> by organization"
>
> id is of type int8, organization is of type varchar(50).
>
> basically the query would return coun(id), and a varchar(50) which is
> organization.
>
> i really dont know what to put on the return type.
>
> what would be the best solution?


If you are in 8.1 you can follow this example from the documentation.
http://www.postgresql.org/docs/8.1/interactive/plpgsql-declarations.html

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN   sum := x + y;   prod := x * y;
END;
$$ LANGUAGE plpgsql;

Then run it like:

SELECT sum, prod FROM sum_n_product(1,2);