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

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?

　　　　　　　　
 

　　　　　　　　alan.nee
　　　　　　　　neemem@gmail.com
　　　　　　　　　　2005-07-13

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)