declare function with IN and OUT parameter as well as refcursor

["Frank Motzkat/IC3S AG" <frank.motzkat@xxxxxxx>]

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