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<http://www.globaldatapoint.com/>

This e-mail is confidential and should not be used by anyone who is not the 
original intended recipient. Global DataPoint Limited does not accept liability 
for any statements made which are clearly the sender's own and not expressly 
made on behalf of Global DataPoint Limited. No contracts may be concluded on 
behalf of Global DataPoint Limited by means of e-mail communication. Global 
DataPoint Limited Registered in England and Wales with registered number 
3739752 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 
4LB

Reply via email to