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)