Re: Returning more than one value from a stored procedure

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(at)gmail(dot)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(at)gmx(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice
>>
>
>