Redirect RAISE NOTICE and errors to separate log file

[Nikhil Ingale <niks.bgm@xxxxxxxxx>]

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.

Thanks & Regards,

Nik