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

[Alvaro Herrera <alvherre@xxxxxxxxxxxxxx>]

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)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings