CODEWITHSUNDEEP
postgresql
Mano
Mano

Reputation: 711

Call Postgres procedure / function with refcursor input

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?

Upvotes: 0

Views: 1570

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247665

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

Upvotes: 1

Related Questions