Skip site navigation (1) Skip section navigation (2)

Re: Returning more than one value from a stored procedure

From: Atif Jung <atifjung(at)gmail(dot)com>
To: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Returning more than one value from a stored procedure
Date: 2010-07-08 13:33:23
Message-ID: AANLkTinKZfATbTg--VU0S-ggcibj0Uf5zxmwpEGaWHWf@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>

In response to

Responses

pgsql-novice by date

Next:From: Atif JungDate: 2010-07-08 13:52:53
Subject: Re: Returning more than one value from a stored procedure
Previous:From: Thomas KellererDate: 2010-07-08 11:53:58
Subject: Re: Returning more than one value from a stored procedure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group