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/>
