Thread: 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

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?

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