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.
CREATE TABLE test(i int, j int); Drop trigger test_trig; INSERT INTO test VALUES(1, 100); INSERT INTO test VALUES(2, 200); CREATE OR REPLACE FUNCTION test_func() RETURNS TRIGGER AS $$ DECLARE c CURSOR FOR SELECT i FROM test FOR UPDATE; v_i numeric; BEGIN OPEN c; FETCH c INTO v_i; UPDATE test SET i=50 WHERE CURRENT OF c; DELETE FROM test WHERE CURRENT OF c; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER test_trig AFTER INSERT OR UPDATE OR DELETE ON test FOR EACH ROW EXECUTE PROCEDURE test_func(); Now when i execute test_func(), it gives error as expected: SELECT test_func(); ERROR: cursor "c" already in use CONTEXT: PL/pgSQL function "test_func" line 4 at open SQL statement "UPDATE test SET i=50 WHERE CURRENT OF $1 " PL/pgSQL function "test_func" line 6 at SQL statement Above error is expected. But after above if i execute any DML DELETE or UPDATE it fails: DELETE FROM test; ERROR: cursor "c" is not positioned on a row CONTEXT: SQL statement "UPDATE test SET i=50 WHERE CURRENT OF $1 " PL/pgSQL function "test_func" line 6 at SQL statement OR update test set i=i+1; ERROR: cursor "c" already in use CONTEXT: PL/pgSQL function "test_func" line 4 at open SQL statement "UPDATE test SET i=50 WHERE CURRENT OF $1 " PL/pgSQL function "test_func" line 6 at SQL statement Comments..?? Thanks, Dharmendra www.enterprisedb.com
