提交,保存点,回滚到PostgreSQL? [英] Commit, savepoint, rollback to in 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( http://www.postgresql.org/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(http://www.postgresql.org/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.
高级是使用以下方法之一打开新连接并对该连接发出单独的事务.在大多数情况下,由于打开一个新的连接,效果并不理想,但在大多数情况下可能效果很好.
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.
这篇关于提交,保存点,回滚到PostgreSQL?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!