Re: Redirect RAISE NOTICE and errors to separate log file

On Fri, Feb 17, 2023 at 8:59 AM Nikhil Ingale <niks.bgm@gmail.com> wrote:

Hi All,

I have below given procedure where I have to redirect the messages and errors to separate log files.

CREATE OR REPLACE PROCEDURE()

language plpgsql

AS $$

Declare

insrtStr character varying(5000);

BEGIN

      insrtStr := 'INSERT into test(a,b,c) SELECT * FROM demo';

      RAISE NOTICE 'insrtStr';

      EXECUTE insrtStr;

      EXCEPTION WHEN OTHERS THEN

      v_sqlerrm := sqlerrm;

   v_sqlstate := sqlstate;

    RAISE NOTICE 'exception: % %  ', v_sqlstate ,  v_sqlerrm ;

END;

$$;

psql -U postgres -d proddb -f test.sql --log-file=test.log

Reason for printing the insrtStr is to keep track of table inserts.

Above is just an example procedure. Procedure that I have written merges the data from server tables.

RAISE NOTICE logs and exceptions are printed on the console. But I want to separate out the logs.

Please provide your valuable feedback.

Leverage your shell.  All non-query output is sent to stderr.

vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -c 'select * from whisper();' postgres

NOTICE:  Hi!
 whisper
---------
 Hello
(1 row)

vagrant@vagrant:~$ /usr/local/pgsql/bin/psql -c 'select * from whisper();' postgres 2>/dev/null
 whisper
---------
 Hello
(1 row)

David J.