Testing Postgresql Functions That Consume and Return Refcursor

Testing PostgreSQL functions that consume and return refcursor

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}

Return multiple resultset from a function in postgres using cursor

This SQL code is fine by itself, but your SQL client is probably sending the whole block in one go as a multi-query string. Then if shows only the result of the last instruction of that sequence, which is the result of commit.

If you tried this in in psql (the primary command-line interface for postgresql), it would show results, since psql parses the SQL buffer to identify queries between ; and sends them as separate statements (use \; to group them).

Is there a proper way to handle cursors returned from a postgresql function in psycopg?

The cursor link you show refers to the Python DB API cursor not the Postgres one. There is an example of how to do what you want here Server side cursor in section:

Note It is also possible to use a named cursor to consume a cursor created in some other way than using the DECLARE executed by execute(). For example, you may have a PL/pgSQL function returning a cursor:

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;

You can read the cursor content by calling the function with a regular, non-named, Psycopg cursor:

cur1 = conn.cursor()
cur1.callproc('reffunc', ['curname'])

and then use a named cursor in the same transaction to “steal the cursor”:

cur2 = conn.cursor('curname')
for record in cur2:     # or cur2.fetchone, fetchmany...
    # do something with record
    pass

UPDATE

Be sure and close the named cursor(cur2) to release the server side cursor. So:

cur2.close()

How to get result set from PostgreSQL stored procedure?

As of Postgres 13, returning from a PROCEDURE is still very limited. See:

• How to return a value from a stored procedure (not function)?

Most likely, you fell for the widespread misnomer "stored procedure" and really want a FUNCTION instead, which can return a value, a row or a set according to its declaration.

• In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?
• How to return result of a SELECT inside a function in PostgreSQL?

Would work like this:

CREATE OR REPLACE FUNCTION public.testSpCrud(
    fnam text,
    lnam text,
    id integer,
    condition integer)
  RETURNS SETOF Employee LANGUAGE plpgsql AS
$func$
BEGIN
   CASE condition
   WHEN 1 THEN
      INSERT INTO public.employee(
       employeeid, lname, fname, securitylevel, employeepassword, hphonearea, hphone, cphonearea, cphone, street, city, state, zipcode, extzip, name, email, groomerid, type, commission, inactive, carrierid, notoallemployees, languageid, isdogwalker, ispetsitter, ismobilegroomer, ssma_timestamp)
       VALUES (4,  'Test', 'Test', 2, 2, 32, 32, 32, 32, 32, 32,32, 32, 32, 22, 22, 2, 2, 2, false, 223,true, 223, true, true, true, '2019-08-27');

   WHEN 2 THEN
      DELETE FROM Employee WHERE employeeid=id;

   WHEN 3 THEN
      UPDATE Employee SET fname='Test' WHERE employeeid=id;

   WHEN 4 THEN
      RETURN QUERY
      SELECT * FROM Employee;

   ELSE
      RAISE EXCEPTION 'Unexpected condition value %!', condition;
   END CASE;
END
$func$;

Simplified with a CASE construct while being at it, and added an ELSE clause. Adapt to your needs.

Call with:

SELECT * FROM public.testSpCrud(...);

Aside: all variable names of a plpgsql block are visible inside nested SQL DML commands. A variable named id is a problem waiting to happen. I suggest a safer naming convention, and / or table-qualify all column names in DML statements. One popular naming convention is to prepend variable names with an underscore. Like: _id.

And consider legal, lower-case identifiers in SQL and PL/pgSQL.

• Are PostgreSQL column names case-sensitive?

Get multiple postgresql function returned cursors from Django

Experimentally, this seems to do the trick with bare psycopg2. The cursor provided by Django should be more or less compatible with a psycopg2 cursor:

import psycopg2
from psycopg2 import sql

create_sql = """
CREATE OR REPLACE FUNCTION multi_cur() RETURNS SETOF refcursor AS $$
DECLARE cursor1 refcursor;
DECLARE cursor2 refcursor;
BEGIN
    OPEN cursor1 FOR SELECT x * x FROM generate_series(1, 10) AS x;
    RETURN NEXT cursor1;
    
    OPEN cursor2 FOR SELECT SQRT(x) FROM generate_series(1, 10) AS x;
    RETURN NEXT cursor2;
END;
$$ LANGUAGE plpgsql;
"""

with psycopg2.connect(dbname='postgres') as conn:
    with conn.cursor() as curs:
        curs.execute(create_sql)
        curs.execute("SELECT * FROM multi_cur()")
        # Get the cursor names (['<unnamed portal 1>', '<unnamed portal 2>'])
        cursor_names = [r[0] for r in curs]
        # Fetch all results from those cursors
        for name in cursor_names:
            curs.execute(sql.SQL("FETCH ALL FROM {}").format(sql.Identifier(name)))
            print([r[0] for r in curs])
            for row in curs:
                print(row)

The output is

[1, 4, 9, 16, 25, 36, 49, 64, 81, 100]
[1.0, 1.4142135623730951, 1.7320508075688772, 2.0, 2.23606797749979, 2.449489742783178, 2.6457513110645907, 2.8284271247461903, 3.0, 3.1622776601683795]

quite as expected.

Refcursor in pl/pgsql

Sure it's possible to return without cursor.

Example:

CREATE FUNCTION reffunc(in_c1 text) RETURNS setof test AS '
DECLARE
    temprec test;
BEGIN
    FOR temprec in SELECT col FROM test WHERE c1 = in_c1 LOOP
        return next temprec;
    END LOOP;
    RETURN;
END;
' LANGUAGE plpgsql;
select * from reffunc('funccursor');

If you're using 8.4 you can also use "RETURN QUERY" which is even nicer.

how to handle refcursor not retrieving data in PGSQL

You don't need a dynamic query when you only have one test case to cover = 'scientist'. In this case, you can simply do :

create or replace function function_1( In tblname varchar(15))
 returns setof scientist language plpgsql as $$
begin
    if tblname ilike 'scientist' then
         return query
           select * from scientist ;
   end if;
end;
$$;

How to read multiple refcursor return by other procedure to another procedure

Unfortunately, you cannot use the FOR <recordvar> IN <cursor> loop, because it only works for bound cursors (which refcursors are not).

But you can still loop through them, with the old-fashioned FETCH:

declare
  rec record;
  cur refcursor;
begin
  for cur in select returns_multiple_cursor() loop
    loop
      fetch next from cur into rec;
      exit when not found;
      -- do whatever you want with the single rows here
    end loop;
    close cur;
  end loop;
end

Unfortunately, there is still another limitation: PostgreSQL caches the first cursor's plan (at least, it seems it does something like that), so you must use cursors, which uses the same column types (you'll have to use the same column names anyway, to be able to refer them in the inner loop, like rec.col1).

Complete, working example: http://rextester.com/FNWG91106 (see f.ex. what happens, when you remove casting from the cursors' queries).

If you have fix number of cursors (like in your example), but differently structured underlying queries, it might be easier to declare your returns_multiple_cursor as:

create or replace  function returns_multiple_cursor(out first_cur refcursor,
                                                    out second_cur refcursor)
    -- returns record (optional)
    language plpgsql
-- ...

This way, you could access your cursors more directly in the calling context.

Update: it seems that when you don't use explicit column names, just generic record processing (via f.ex. JSON or hstore), plan caching does not cause any trouble:

http://rextester.com/QHR6096

---

Related Topics