Sample Header Ad - 728x90

Postgres 11 - Fetching results after a transactiom

4 votes
2 answers
1465 views
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?
Asked by deviantxdes (141 rep)
Jul 1, 2018, 03:40 AM
Last activity: Sep 23, 2019, 09:03 PM