Обсуждение: Logging in function with exception

Hello,

I write funktions in pl/pgsql and want to abort it. For this I use raise
exception, to undo all changes for this transaction. Now I want to log
these exceptions somewhere.

DECLARE
  a integer;
  log_id integer;
BEGIN
  SELECT a INTO b FROM c WHERE d = 10;
  IF NOT FOUND THEN
    INSERT INTO log (logtype, logtext)
      VALUES ('error', 'Dit not found 10 in table')
      RETURNING id INTO log_id;
    RAISE EXCEPTION 'internal_error: %', log_id;
  END IF;
  -- anything else
END;

I know that these logs well be discard, if the commit rolls back.

Is there a possible way to implement something like this?

Thanks,
  Gerhard

One of the simplest ways to do it is with plProxy call into same database in exception block.
Get plproxy installed in your database and after that it is as simple as function call.
test=# create table log ( msg text );
CREATE TABLE
test=# create function add_logg ( i_msg text ) returns void as $$ insert into log (msg) values ($1); $$ language sql;
CREATE FUNCTION
test=# create or replace function logg ( i_msg text ) returns void as $$ connect 'dbname=test'; select add_logg($1); $$ language plproxy;
CREATE FUNCTION
test=# select logg('test2'); logg
...
test=# select * from log;
  msg 
-------
 test2

test=# create or replace function example1() returns void as $$ begin perform logg('test3'); raise exception 'test3'; end; $$ language plpgsql;
CREATE FUNCTION
test=# select example1();ERROR:  test3
test=# select * from log;
  msg 
-------
 test2
 test3


On Mon, Nov 10, 2008 at 5:31 PM, Gerhard Heift <ml-postgresql-20081012-3518@gheift.de> wrote: