Is it possible to use OUT/INOUT parameter along with return refcursor in POSTGRESQL?

Question

Here is an example snippet. I am trying to have a INOUT parameter and also trying to return a refcursor.

CREATE OR REPLACE FUNCTION reffunc2(IN key int, INOUT name int) 
RETURNS refcursor 
AS $$
DECLARE
ref refcursor;
BEGIN
name = 123;
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
$$ 
LANGUAGE plpgsql;

Answer 1

Specifying an OUT or INOUT parameter is the same as specifying a return value, so the two definitions have to match:

• with a single OUT parameter, RETURNS must specify the type of that parameter

• with more than one OUT parameter, you must use RETURNS record

You cannot specify one result column as OUT parameter and the other one as result.

Use this:

CREATE FUNCTION reffunc2(
   IN key int,
   INOUT name int,
   OUT x refcursor
) RETURNS record

In the function body, you must assign values to name and x and use RETURN without argument.