From: | "Dharmendra Goyal" <dharmendra(dot)goyal(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | DML fails after updatable cursor is used with trigger returning function |
Date: | 2007-10-31 09:58:34 |
Message-ID: | f87e6d710710310258o76194ea5x1d7de09e44aa59cb@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gokulakannan Somasundaram | 2007-10-31 10:31:07 | Clarification on a Time travel feature |
Previous Message | J. Andrew Rogers | 2007-10-31 07:00:45 | Re: Opportunity for a Radical Changes in Database Software |