postgresqlstored-proceduresplsqlstored-functionsref-cursor

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


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;

Solution

  • 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.