Thread: Returning more than one value from a stored procedure

Returning more than one value from a stored procedure

From
Atif Jung
Date:
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


Re: Returning more than one value from a stored procedure

From
"A. Kretschmer"
Date:
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

Re: Returning more than one value from a stored procedure

From
Atif Jung
Date:
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.
 
Many Thanks

Atif




On 8 July 2010 11:14, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
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

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: Returning more than one value from a stored procedure

From
Tim Landscheidt
Date:
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

Re: Returning more than one value from a stored procedure

From
"A. Kretschmer"
Date:
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

Re: Returning more than one value from a stored procedure

From
Thomas Kellerer
Date:
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;



Re: Returning more than one value from a stored procedure

From
Thomas Kellerer
Date:
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')



Re: Returning more than one value from a stored procedure

From
Atif Jung
Date:
Thanks Thomas et al,
 
your advice and pointer have been of great help. to minimise my code change I've decide to use the RETURNS TABLE option, but I now have the following problem.
 
My procedure is as follows:
 
CREATE or REPLACE FUNCTION myproc(var1 CHAR(9), var2 (CHAR4)) RETURNS TABLE (result INTEGER, reply TEXT)) AS $$
DECLARE
   replyx TEXT;
   result INTEGER;
BEGIN
   replyx := 'HELLO WORLD';
   result := 150
 
   RETURN QUERY SELECT result, replyx;
END;
$$ LANGUAGE plpgsql;
 
I call it from ECPG (EMBEDDED SQL IN C) as follows:
 
EXEC SQL BEGIN DECLARE SECTION;
   int iResult
   char acReply[1000];
EXEC SQL END DECLARE SECTION;
 
EXEC SQL SELECT myproc ('abcdefghi', 'test') INTO :iResult, :acReply;
 
The error I get when I run the code is:
 
SQLSTATE:         42804
ERROR MESSAGE: invalid input syntax for type int: "(150,"HELLO
 
What am I doing wrong?
 
Many Thanks

Atif




On 8 July 2010 12:53, Thomas Kellerer <spam_eater@gmx.net> wrote:
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')





--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: Returning more than one value from a stored procedure

From
Atif Jung
Date:
Thomas,
 
I figured out what the problem was, the function call returns a string, not as I initially thought an INT and a  string.
 
Kind Rgds

Atif




On 8 July 2010 14:33, Atif Jung <atifjung@gmail.com> wrote:
Thanks Thomas et al,
 
your advice and pointer have been of great help. to minimise my code change I've decide to use the RETURNS TABLE option, but I now have the following problem.
 
My procedure is as follows:
 
CREATE or REPLACE FUNCTION myproc(var1 CHAR(9), var2 (CHAR4)) RETURNS TABLE (result INTEGER, reply TEXT)) AS $$
DECLARE
   replyx TEXT;
   result INTEGER;
BEGIN
   replyx := 'HELLO WORLD';
   result := 150
 
   RETURN QUERY SELECT result, replyx;
END;
$$ LANGUAGE plpgsql;
 
I call it from ECPG (EMBEDDED SQL IN C) as follows:
 
EXEC SQL BEGIN DECLARE SECTION;
   int iResult
   char acReply[1000];
EXEC SQL END DECLARE SECTION;
 
EXEC SQL SELECT myproc ('abcdefghi', 'test') INTO :iResult, :acReply;
 
The error I get when I run the code is:
 
SQLSTATE:         42804
ERROR MESSAGE: invalid input syntax for type int: "(150,"HELLO
 
What am I doing wrong?
 
Many Thanks

Atif




On 8 July 2010 12:53, Thomas Kellerer <spam_eater@gmx.net> wrote:
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')





--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice