Re: My function run successfully with cursor, but can't change table - Mailing list pgsql-general

高健 <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