Re: can you tell me how to use rollback work in 'pgsql' function? and give me a exsample?

On Wed, Jul 13, 2005 at 01:55:39PM +0800, Nee.mem(????) wrote:
>
> i see you wrote on this page
> http://archives.postgresql.org/pgsql-general/2005-07/msg00319.php
>
> test exsample:
>     create or replace function test()
>     returns void as
>     '
>     begin
>         delete from regiondata;
>         rollback;
>     end;
>     'language 'plpgsql';

> but exception a error:    CONTEXT:  PL/pgSQL function "test" line 3 at SQL statement
> Use others' words :
> >It is important not to confuse the use of BEGIN/END for grouping statements
> > in PL/pgSQL with the database commands for transaction control. PL/pgSQL's
> > BEGIN/END are only for grouping; they do not start or end a transaction
> and can you tell me how to use rollback work in 'pgsql' function?  and give me a exsample?

You don't use ROLLBACK in a PL/pgSQL function.  It's not supported.  You
need to use an exception instead.  See the PL/pgSQL documentation.  A
trivial example:

     create or replace function test()
    returns void as
    $$
    begin
        delete from regiondata;
        raise exception 'oops, deleted the whole table';
        return;
    exception when others then
        null;
        return;
    end;
    $$ language 'plpgsql';

Or, more generally useful,

     create or replace function test()
    returns void as
    $$
    begin
        begin
            delete from regiondata;
            raise exception 'oops, deleted the whole table';
        exception when others then
            null;
        end;
        return;
    end;
    $$ language 'plpgsql';

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Y eso te lo doy firmado con mis lágrimas" (Fiebre del Loco)