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?