Re: Logging in function with exception

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(at)gheift(dot)de> wrote:

> 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
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFJGFPSa8fhU24j2fkRAucPAJsGzm+b5MiBUdZjw+w3Krbv08wskgCeMj3s
> nY37c9nwSiOcTf/XUf/47Wo=
> =o6PG
> -----END PGP SIGNATURE-----
>
>