Обсуждение: DML fails after updatable cursor is used with trigger returning function

I created one function which updates a table using updatable cursor. I wrote one trigger also on the same table. When i
executethe function it gives expected results.  But after that all DMLs fail. <br /><br /> CREATE TABLE test(i int, j
int);<br /> Drop trigger test_trig; <br /> INSERT INTO test VALUES(1, 100); <br /> INSERT INTO test VALUES(2, 200); <br
/><br/> CREATE OR REPLACE FUNCTION test_func() <br /> RETURNS TRIGGER <br /> AS $$ <br /> DECLARE c CURSOR FOR SELECT i
FROMtest FOR UPDATE; <br /> v_i numeric; <br /> BEGIN <br /> OPEN c; <br /> FETCH c INTO v_i; <br /> UPDATE test SET
i=50WHERE CURRENT OF c; <br /> DELETE FROM test WHERE CURRENT OF c; <br /> RETURN NULL; <br /> END; $$ LANGUAGE
plpgsql;<br /><br /> CREATE TRIGGER test_trig <br /> AFTER INSERT OR UPDATE OR DELETE ON test <br />    FOR EACH ROW
EXECUTEPROCEDURE test_func(); <br /><br /> Now when i execute test_func(), it gives error as expected: <br /> SELECT
test_func();<br /> ERROR:  cursor "c" already in use <br /> CONTEXT:  PL/pgSQL function "test_func" line 4 at open <br
/>SQL statement "UPDATE test SET i=50 WHERE CURRENT OF  $1 " <br /> PL/pgSQL function "test_func" line 6 at SQL
statement<br /><br /> Above error is expected. <br /><br /> But after above if i execute any DML DELETE or UPDATE it
fails:<br /> DELETE FROM test; <br /> ERROR:  cursor "c" is not positioned on a row <br /> CONTEXT:  SQL statement
"UPDATEtest SET i=50 WHERE CURRENT OF  $1 " <br /> PL/pgSQL function "test_func" line 6 at SQL statement <br /><br />
OR<br /> update test set i=i+1; <br /> ERROR:  cursor "c" already in use <br /> CONTEXT:  PL/pgSQL function "test_func"
line4 at open <br /> SQL statement "UPDATE test SET i=50 WHERE CURRENT OF  $1 " <br /> PL/pgSQL function "test_func"
line6 at SQL statement <br /><br /> Comments..?? <br /><br /> Thanks,<br /> Dharmendra<br /><a
href="http://www.enterprisedb.com">www.enterprisedb.com</a><br/> 

"Dharmendra Goyal" <dharmendra.goyal@gmail.com> writes:
> I created one function which updates a table using updatable cursor. I wrote
> one trigger also on the same table. When i execute the function it gives
> expected results.  But after that all DMLs fail.

The problem is that your trigger function recursively invokes itself.

If you used an unbound cursor variable (so that the portal name gets
selected dynamically) you could avoid the conflict of cursor name
between inner and outer executions, but you'd still need to do something
about avoiding infinite recursion.  Also, closing a cursor when done
with it would be a real good idea.
        regards, tom lane