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)