good exception handling archiecutre

How can we do error logging in Postgres. I am trying to create a LOG_AND_STOP method which would be generic to the code. I have pasted the code I have written. This code returns no data which is understandable as the error is thrown to the external world.

Can I write the code somehow.

CREATE SCHEMA test

  AUTHORIZATION postgres;

CREATE TABLE test.error_log

(

  error_desc text

);

CREATE OR REPLACE FUNCTION test.log_and_stop(err_desc text)

  RETURNS void AS

$BODY$

Declare

x integer;

begin

begin

insert into test.error_log values (err_desc);

end;

begin

raise exception '%',err_desc;

end;

end;

$BODY$

  LANGUAGE 'plpgsql' VOLATILE

  COST 100;

ALTER FUNCTION test.log_and_stop(text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION test.f1()

  RETURNS void AS

$BODY$

Declare

x integer;

begin

x:=1;

x:=x/0;

exception

when others then

perform test.log_and_stop('error occured in function f1');

end;

$BODY$

  LANGUAGE 'plpgsql' VOLATILE

  COST 100;

ALTER FUNCTION test.f1() OWNER TO postgres;

select test.f1();

/*----------------------------output-------------------------*/

-- ERROR:  error occured in function f1

-- CONTEXT:  SQL statement "SELECT  test.log_and_stop('error occured in function f1')"

-- PL/pgSQL function "f1" line 8 at PERFORM

--

--

-- ********** Error **********

--

-- ERROR: error occured in function f1

-- SQL state: P0001

-- Context: SQL statement "SELECT  test.log_and_stop('error occured in function f1')"

-- PL/pgSQL function "f1" line 8 at PERFORM

/*-------------------------------------------------------------*/

select * from test.error_log; --no data found;

Regards,

Atul Goel

SENIOR DEVELOPER

Global DataPoint

Middlesex House, 34-42 Cleveland Street

London W1T 4LB, UK

T: +44 (0)20 7079 4827

M: +44 (0)7846765098

www.globaldatapoint.com