Thread: declare function with IN and OUT parameter as well as refcursor
Hi community,
I would like to declare a function with IN and OUT parameters as well as a refcursor as return value.
For example something like this:
This doesn’t work. It gives the compilation error ‘ERROR: function result type must be integer because of OUT parameters’
CREATE OR REPLACE FUNCTION reffunc2(IN key int, INOUT name int) RETURNS refcursor
AS $$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
$$ LANGUAGE plpgsql;
This works …
CREATE OR REPLACE FUNCTION reffunc2(IN key int) RETURNS refcursor
AS $$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
$$ LANGUAGE plpgsql;
I’m wondering how I should declare such a function, or isn’t possible at all with postgres 8.1 ? With MS-Sql and Oracle such functions are no problem at all.
Thanks a lot for any reply in advance !
Cheers,
frank
Re: declare function with IN and OUT parameter as well as refcursor
On Thu, Nov 24, 2005 at 09:26:07AM +0100, Frank Motzkat/IC3S AG wrote: > Hi community, > > I would like to declare a function with IN and OUT parameters as well as a refcursor as return value. > > For example something like this: > > This doesn???t work. It gives the compilation error ???ERROR: function result type must be integer because of OUT parameters??? <snip> Well in that case you've said the output is an integer, and you're returning a refcursor, hence the error. In the second case you don't have any out parameters, so it understands you're trying to return the refcursor. I suppose you could declare the refcursor as an OUT parameter, or return the integer in the first case, Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.