Call Postgres procedure / function with refcursor input

Question

I'm porting an Oracle function to Postgres which has one of the input parameters as refcursor type. The ported Postgres function looks as below.

create or replace function gt_mlt_dtls(dtl_cur IN REFCURSOR, flag TEXT, dt DATE)
returns........

How do I pass the value for dtl_cur parameter as REFCURSOR? In Oracle, CURSOR(some select query) works for inputting a refcursor. Is it possible in PostgreSQL?

Laurenz Albe · Accepted Answer · 2021-02-22 07:58:37Z

1

You could DECLARE the cursor in SQL and pass its name to the procedure:

BEGIN;

DECLARE c CURSOR FOR SELECT ...;

CALL gt_mlt_dtls('c', 'text', current_date);

COMMIT;

To call it from another procedure, call

DECLARE
   c refcursor;
BEGIN
   OPEN c FOR SELECT ...;
   CALL gt_mlt_dtls(c, ...);

edited Feb 22, 2021 at 7:58

answered Feb 19, 2021 at 20:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mano Over a year ago

Thanks, how can I call this function(with refcursor input parameter) inside another procedure ?

Collectives™ on Stack Overflow

Call Postgres procedure / function with refcursor input

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related