Re: Redirect RAISE NOTICE and errors to separate log file

On Fri, Feb 17, 2023 at 8:59 AM Nikhil Ingale <niks(dot)bgm(at)gmail(dot)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(at)vagrant:~$ /usr/local/pgsql/bin/psql -c 'select * from whisper();'
postgres
NOTICE: Hi!
whisper
---------
Hello
(1 row)

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

David J.