Re: Redirect RAISE NOTICE and errors to separate log file

I need the non query output in the user defined log file.
How can I achieve that?

On Fri, Feb 17, 2023 at 9:57 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

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