Re: Oracle error log table equivalent in postgresql

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.

On Fri, Aug 6, 2021, 6:20 PM <066ce286(at)free(dot)fr> wrote:

>
> > 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.
>