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