Is having RefCursor as an OUT parameter of a Postgresql function, allowed?

Question

I know the following is possible. i.e I can have a ref cursor as a return value in Postgresql.

CREATE FUNCTION employeefunc(int) RETURNS refcursor AS '
DECLARE ref refcursor;  
BEGIN
OPEN ref FOR SELECT * FROM employee where id = $1;
RETURN ref;
END;

But can we have a ref cursor as an OUT parameter in a postgresql function?

For your reference, following the the Oracle equivalent of what I'm looking for.

create or replace procedure employeefunc(rc out sys_refcursor) as
begin
open rc for 'select * from employee';
end;

Answer 1

There is no CREATE PROCEDURE in Postgresql like Oracle PL/SQL. You may create a FUNCTION with OUT parameter as REFCURSOR, but the RETURN TYPE should be specified as REFCURSOR.

CREATE FUNCTION employeefunc (rc_out OUT refcursor)
RETURNS refcursor
AS
$$
BEGIN
    OPEN rc_out
    FOR
    SELECT *
    FROM employees;
END;
$$ LANGUAGE plpgsql;

As you could see, there is no RETURN statement. That's because if the function has OUT variables, you can't end the function using a RETURN statement with a value, but using a simple RETURN; is valid.

Answer 2

Postgresql stored procedures does not support OUT parameters like other Servers do. But it supports INOUT parameters which we can use to perform output tasks.

Note: Stored procedures in Postgresql are introduced in Version 11; So, if you are using an earlier version, please upgrade it before using stored procedures.

Thank you !!