From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Returning more than one value from a stored procedure |
Date: | 2010-07-08 11:53:58 |
Message-ID: | i14e8n$e12$1@dough.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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')
From | Date | Subject | |
---|---|---|---|
Next Message | Atif Jung | 2010-07-08 13:33:23 | Re: Returning more than one value from a stored procedure |
Previous Message | Thomas Kellerer | 2010-07-08 11:43:58 | Re: Returning more than one value from a stored procedure |