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