Thanks. I got how to capture errmsg, errcode etc into error log table however my aim is to insert rejected rows too in error log table with all its column data. If you know error log table feature in oracle then you could relate what I am trying to achieve here.
> Thanks, but it solve just part of the problem. In oracle insert > statement executes and rejected rows are inserted in error log table > in same transaction. But, its similar feature or workaround seems > hard in postgresql.
It's not hard.
You may either intercept the exception( EXCEPTION WHEN OTHER ...) so that the transaction will not be rollbacked, so you can easily insert into another table what you want.
But, if you want to rollback the transaction with a RAISE EXCEPTION, you just have to insert in your log table using another transaction, for example with a loopback dblink.
Something like :
select setting INTO l_port FROM pg_settings WHERE name = 'port'; PERFORM * FROM dblink( FORMAT('dbname=%s user=%s port=%s',current_database(), current_user, l_port), FORMAT('INSERT INTO TRACE_ERROR(ernno,errmsg,errcontext) VALUES (%L,%L,%L);',l_errno,l_errmsg,l_errctx) ) AS p (ret text);
Otherwise, when you rollback, the INSERT into the error log table will be also rollbacked.