Testing PostgreSQL functions that consume and return refcursor

Question

I want to check the results of a Postgres function (changing a function is not possible).

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

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

Now I want to create a little test in Postgres to get the results of this FUNCTION. Something like the code below (this is my approach, but it does not work):

 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 make this work? A general solution that can be used to test this type of function (which get the cursor and return the cursor?

And if we do not know the returned rowtype, how can we do this?

What is the best way to debug things like this in PostgresQL

Answer 1

Q1

Your “little test” may 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; 

Run COMMIT / ROLLBACK after checking the results. Most clients only display the result of a lat command.

See the chapter Returning Cursors for More Information .

Q2

And if we do not know the returned rowtype, how can we do this?

Since you only want to check the results, you can translate the entire record to text . This way you avoid the problem of dynamic return types for the whole function.

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) AS -- INOUT param :) $func$ BEGIN OPEN ret FOR SELECT * FROM a; END $func$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION ctest() RETURNS SETOF text 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$ LANGUAGE plpgsql; 

→ SQLfiddle

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