Thread: Returning more than one value from a stored procedure

In response to Atif Jung :
> 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?
>  

You should use IN/OUT - Parameters, foro example:

test=# create or replace function atif(in int, in text, out a int, out b text) returns record as $$begin a=1; b='hello
world';return; end;$$language plpgsql; 
CREATE FUNCTION
test=*# select * from atif(0,'test');
 a |      b
---+-------------
 1 | hello world
(1 row)


--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Atif Jung <atifjung@gmail.com> wrote:

> Thanks Andreas but is there no alternative?

> I'm porting from an INFORMIX database which allows the return of more than
> one value from a stored procedure without having to use in/out
> parameters. The procedure is called from several places across the system
> and will be time consuming to change all instances to include the new out
> parameters. To be able to return more than one parameter will be of great
> help to me, if it's possible.
> [...]

Andreas' example returns more than one value. Perhaps you
could clarify where your problem lies.

Tim

In response to Atif Jung :
> Thanks Andreas but is there no alternative?
>  
> I'm porting from an INFORMIX database which allows the return of more than one
> value from a stored procedure without having to use in/out parameters. The
> procedure is called from several places across the system and will be time
> consuming to change all instances to include the new out parameters. To be able
> to return more than one parameter will be of great help to me, if it's
> possible.

Other solution, but new problem for you:

test=# create or replace function atif(int, text) returns record as
$$declare r record; begin select into r 1::int, 'hello world'::text; return r; end;$$language plpgsql;
CREATE FUNCTION
test=*# select * from atif (0,'test') as foo(a int, b text);
 a |      b
---+-------------
 1 | hello world
(1 row)

test=*# select * from atif (0,'test');
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select * from atif (0,'test');


Now you have to define the result-type later, but you have to define
that.

Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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;

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')