Postgres 11 - Fetching results after a transactiom

Question

With the recent update of Postgres 11. It now supports procedures. Transactions are finally supported in stored procedures in Postgres. However, I have been trying to perform a transaction and retrieving a refcursor as the result.

CREATE OR REPLACE PROCEDURE testproc(pid text,pname text,INOUT cresults refcursor)
 LANGUAGE plpgsql
AS $procedure$

BEGIN
 cresults:= 'cur';  
 begin
    update "testtable" set id=pid , name=pname where id=pid;
  commit;
 end;
    OPEN cresults for select * from oureadata.CMS_CATEGORY limit 1;
end;
$procedure$

I understand that fetching of refcursor have to be in a transaction. This is how i execute the procedure.

BEGIN;
Call oureadata.testproc('1','2','');
fetch all in cur;
commit;

When i try to fetch the cursor, it throws an exception "ERROR: Invalid transaction termination"

---

But if i remove the commit from the procedure. I can actually execute the procedure and fetch the result of the refcursor. (The above is just an example, as i have other more complex transaction which will return refcursor after)

So my question is, can a procedure return a INOUT refcursor result after a transaction is done?

Answer 1

From what I understand, in order to use the cursor after your procedure ends, you need to be on the same transaction you was when calling the procedure. Your code use two different transactions: the first one is started with BEGIN just before calling the procedure, and it ends at the COMMIT. The second transaction starts automatically after the COMMIT in your procedure and continue until the COMMIT after your FETCH.

Answer 2

I worked around the requirement to be on the same transaction by writing a bash script which executed my SQL scripts using PSQL. It seems to work ok. https://stackoverflow.com/a/58022481/494635