Re: Returning more than one value from a stored procedure

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