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;

Kaushik Nayak · Accepted Answer · 2018-03-10T11:14:55.020

5

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.

edited Mar 10 '18 at 11:14

answered Mar 10 '18 at 10:05

Kaushik Nayak

30,772
5
32
45

1

Thanks for the explanation! I have another question. Say I'm calling this procedure through java. In order to obtain a ref cursor returned as an OUT param or with a RETURN statement, in both cases we'll have to register an out parameter (statment.registerOutParameter). Is the above statement correct? – Manuri Perera Mar 10 '18 at 11:36
@Snitch : You are welcome. As for your question, Java is not my area. You may google it or ask that as a separate question. – Kaushik Nayak Mar 10 '18 at 11:50
Cool, thanks again Kaushik! I would have upvoted too if I had permission! You saved a lot of my time! – Manuri Perera Mar 10 '18 at 12:09

score 0 · Answer 2 · answered May 08 '20 at 04:38

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 !!

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

2 Answers2