Testing PostgreSQL functions that consume and return refcursor

Question

I want to test results of a Postgres function (changing the function is not a possibility).

The function receives as arguments a REFCURSOR and several other things and returns the same RECURSOR.

get_function_that_returns_cursor(ret, 4100, 'SOMETHING', 123465)

Now I want to create a small test in Postgres to get the results of this FUNCTION. Something Like the code below (this is my approach but it is not working):

DO $$ DECLARE
 ret REFCURSOR; 
 row_to_read table_it_will_return%ROWTYPE ;
BEGIN
 PERFORM get_function_that_returns_cursor(ret, 4100, 'SOMETHING', 123465);
-- OR SELECT get_function_that_returns_cursor(ret, 4100, 'SOMETHING', 123465) INTO ret
 FOR row_to_read IN SELECT * FROM ret LOOP
   -- (...)
   RAISE NOTICE 'Row read...';
 END LOOP;
 CLOSE ret;
END $$;

Any suggestion on how to get this to work? A generic solution that can be used for testing this type of functions (that get a Cursor and return a Cursor?

And if we don't know the rowtype that is being returned how could we do it?

Propulsion

Working with dynamic rowsets is difficult in PL/PgSQL. It doesn't really deal well with them. I'd recommend consuming the refcursor in a pl/python, pl/perl, etc function and doing your testing and evaluation there. – Septi

Answer 1

Q1

Your "small test" can be plain SQL:

BEGIN;
SELECT get_function_that_returns_cursor('ret', 4100, 'foo', 123); -- note: 'ret'
FETCH ALL IN ret; -- works for any rowtype

COMMIT;  -- or ROLLBACK;

Execute COMMIT / ROLLBACK after you inspected the results. Most clients only display the result of the lat command.

More in the chapter Returning Cursors of the manual.

Q2

And if we don't know the rowtype that is being returned how could we do it?

Since you only want to inspect the results, you could cast the whole record to text. This way you avoid the problem with dynamic return types for the function altogether.

Consider this demo:

CREATE TABLE a (a_id int PRIMARY KEY, a text);
INSERT INTO a VALUES (1, 'foo'), (2, 'bar');


CREATE OR REPLACE FUNCTION reffunc(INOUT ret refcursor)
  LANGUAGE plpgsql AS
$func$
BEGIN
   OPEN ret FOR SELECT * FROM a;
END
$func$;


CREATE OR REPLACE FUNCTION ctest()
  RETURNS SETOF text
  LANGUAGE plpgsql AS
$func$
DECLARE
   curs1 refcursor;
   rec   record;
BEGIN
   curs1 := reffunc('ret');   -- simple assignment
  
   LOOP
      FETCH curs1 INTO rec;
      EXIT WHEN NOT FOUND;    -- note the placement!
      RETURN NEXT rec::text;
   END LOOP;
END
$func$;

db<>fiddle here
_{Old sqlfiddle}

Remind

Answer 2

This worked for what I wanted:

DO $$ DECLARE
 mycursor REFCURSOR;
 rec RECORD;
BEGIN
 SELECT 'ret' INTO mycursor FROM get_function_that_returns_cursor('ret'::REFCURSOR, 4100, 'SOMETHING', 123465);
 WHILE (FOUND) LOOP
   FETCH mycursor INTO rec;
   RAISE NOTICE 'Row read. Data: % ', rec.collumn_name;
 END LOOP;
END $$

Propulsion