提交，保存点，回滚到PostgreSQL?

本文介绍了提交，保存点，回滚到PostgreSQL?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

有人可以向我解释为什么此函数中的COMMIT返回EXCEPTION吗?

Can someone please explain to me why does COMMIT in this function returns EXCEPTION ?

DECLARE XNar CURSOR (forDATE Varchar) IS SELECT NARUCENO, ISPORUKA_ID FROM XDATA_NARUDZBE WHERE TO_CHAR(XDATA_NARUDZBE.DATUM, 'DD.MM.YYYY') = forDATE; LastDate DATE; OutResult INTEGER; curNAR NUMERIC; curISP VARCHAR; RXNar RECORD; BEGIN OutResult := 1; SELECT MAX(DATUM) INTO LastDate FROM XDATA_NARUDZBE; FOR RXNar IN XNar(TO_CHAR(LastDate, 'DD.MM.YYYY')) LOOP IF (RXNar.NARUCENO <> 0) AND (RXNar.ISPORUKA_ID = 'R01') THEN UPDATE NARUDZBE SET ISPORUCENO = RXNar.NARUCENO WHERE NARUDZBE.PP_ID = RXNar.PP_ID AND NARUDZBE.ART_ID = RXNar.ART_ID AND NARUDZBE.ISPORUKA_ID = 'R01'; END IF; END LOOP; COMMIT; <--- ???? RETURN OutResult; EXCEPTION WHEN OTHERS THEN OUTRESULT := 0; RAISE; RETURN OutResult; END;

为什么函数中存在EXCEPTION块时为什么不能使用ROLLBACK TO SavePoint?

and why I can not use ROLLBACK TO SavePoint when EXCEPTION block exists in function?

推荐答案

Frank Heikens回答说，您不能使用plpgsql提交plpgsql存储的函数/过程.但是，您可以通过使用dblink( www.postgresql/docs/9.0/interactive/contrib-dblink-connect.html )或其他存储过程语言，例如plperl(不受信任).查看此链接.

You can't commit in a plpgsql stored function/procedure using plpgsql as Frank Heikens answered. You can however work around this issue by using dblink(www.postgresql/docs/9.0/interactive/contrib-dblink-connect.html) or another store procedure language such as plperl(untrusted). Check out this link where this talked about.

postgresql.1045698.n5.nabble/Re-GENERAL-Transactions-within-a-function-body-td1992810.html

高级是使用以下方法之一打开新连接并对该连接发出单独的事务.在大多数情况下，由于打开一个新的连接，效果并不理想，但在大多数情况下可能效果很好.

The high level is you open a new connection using one of these methods and issue a separate transaction on that connection. Works for most cases not ideal because you are opening a new connection, but may work fine for most use cases.