My function run successfully with cursor, but can't change table
Hello:
Would somebody please kindly tell why my function run but can't update
table via cursor:
I have table like this:
create table course_tbl(course_number integer, course_name varchar(4),
instructor varchar(10));
insert into course_tbl values (1,'aaaa','TOM'), (2,'bbbb','JACK');
select * from course_tbl;
course_number | course_name | instructor
---------------+-------------+------------
1 | aaaa | TOM
2 | bbbb | JACK
(2 rows)
And I made a function to access the table and I want to change the table
record:
In my function, I want to update table record whose course_name is equal to
parameter passed in:
-----------------here is my function-------------------------------
CREATE OR REPLACE Function FindCourse
( name_in IN varchar )
RETURNS integer LANGUAGE plpgsql AS $$
DECLARE
cnumber integer;
cinstructor varchar;
c1 CURSOR
FOR
SELECT course_number, instructor
from course_tbl
where course_name = name_in
FOR UPDATE;
BEGIN
BEGIN
open c1;
fetch c1 into cnumber,cinstructor;
IF not found THEN
cnumber := 9999;
ELSE
UPDATE course_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1;
COMMIT;
END IF;
close c1;
EXCEPTION
WHEN OTHERS THEN
END;
RETURN cnumber;
END;$$;
----------------- -------------------------------
I ran the function like this:
postgres=# select FindCourse('aaaa');
findcourse
------------
1
(1 row)
I got returned result of 1, which I think is that I really got the record.
For my update and commit statements, I think I can get instructor changed.
---------------------------------------------
…
UPDATE course_tbl
SET instructor = 'SMITH'
WHERE CURRENT OF c1;
COMMIT;
…
----------------------------------------------
But when I select the table again, I found it not changed.
postgres=# select * from course_tbl;
course_number | course_name | instructor
---------------+-------------+------------
1 | aaaa | TOM
2 | bbbb | JACK
(2 rows)
Anybody know the reason, maybe there are some wrong way by which I use the
cursor.
Thanks!
高健 <luckyjackgao@gmail.com> wrote:
CREATE OR REPLACE Function ...
BEGIN
BEGIN
UPDATE ...
COMMIT;
EXCEPTION
WHEN OTHERS THEN
END;
END;
But when I select the table again, I found it not changed.
Anybody know the reason, maybe there are some wrong way by
which I use the cursor.
It has nothing to do with the way you are using the cursor; your
problem is that you are causing an error by attempting to COMMIT
inside a function (which is not allowed). This rolls back the
subtransaction defined by the BEGIN/EXCEPTION block. You then
suppress any display of the error with the WHEN OTHERS block.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Kevin Grittner wrote on 10.06.2013 15:19:
It has nothing to do with the way you are using the cursor; your
problem is that you are causing an error by attempting to COMMIT
inside a function (which is not allowed). This rolls back the
subtransaction defined by the BEGIN/EXCEPTION block. You then
suppress any display of the error with the WHEN OTHERS block.
I thought you could *never* use commit (or rollback) inside a function?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thomas Kellerer wrote:
Kevin Grittner wrote on 10.06.2013 15:19:
It has nothing to do with the way you are using the cursor; your
problem is that you are causing an error by attempting to COMMIT
inside a function (which is not allowed). This rolls back the
subtransaction defined by the BEGIN/EXCEPTION block. You then
suppress any display of the error with the WHEN OTHERS block.I thought you could *never* use commit (or rollback) inside a function?
You cannot use transaction commands directly, but EXCEPTION blocks
use savepoints internally.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi :
Sorry for replying lately.
I tried to take the commit statement out of the function , and it works
well.
Thank you!
2013/6/10 Kevin Grittner <kgrittn@ymail.com>
Show quoted text
高健 <luckyjackgao@gmail.com> wrote:
CREATE OR REPLACE Function ...
BEGIN
BEGINUPDATE ...
COMMIT;EXCEPTION
WHEN OTHERS THEN
END;
END;But when I select the table again, I found it not changed.
Anybody know the reason, maybe there are some wrong way by
which I use the cursor.It has nothing to do with the way you are using the cursor; your
problem is that you are causing an error by attempting to COMMIT
inside a function (which is not allowed). This rolls back the
subtransaction defined by the BEGIN/EXCEPTION block. You then
suppress any display of the error with the WHEN OTHERS block.--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company