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!