Re: Returning more than one value from a stored procedure - Mailing list pgsql-novice

Thomas Kellerer, 08.07.2010 13:43:
> Atif Jung, 08.07.2010 11:51:
>> Hi,
>> I'm having difficulty working out the correct syntax to return more than
>> one value from a stored procedure. I wish to return an INTGER and a
>> string
>> CREATE or REPLACE FUNCTION testproc(val1 (CHAR9), val2 CHAR(4)) RETURNS
>> INTEGER, CHAR(640) AS $$
>> The above is incorrect but what is the correct syntax?
>> Thanks
>>
>> Atif
>>
>>
>
> If you don't need the power of pl/pgSQL to calculate your result, a
> simple SQL function should work:
>
> CREATE OR REPLACE FUNCTION testproc(val1 char(9), val2 char(4))
> RETURNS TABLE(id integer, some_value text)
> AS
> $$
> SELECT 42, 'your value'::text;
> $$
> LANGUAGE sql;
>

I did hit "Send" too quickly...

If you do need calculations in there (and thus the power of PL/pgSQL), you can do that as well:

CREATE OR REPLACE FUNCTION testproc(val1 char(9), val2 char(4))
RETURNS TABLE(id integer, some_value text)
AS
$$
DECLARE
   return_int integer;
   return_text text;
BEGIN
   return_int := 21 * 2;
   return_text := 'Your input value: ' || val1;

   RETURN QUERY SELECT return_int, return_text;
END
$$
LANGUAGE plpgsql;

Both can be used like this:  select * from testproc('x', 'y')