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@xxxxxxxxx> 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-----
