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 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
Home |
Main Index |
Thread Index