Thread: How to output psql to file AND screen/console

How to output psql to file AND screen/console

From
Edwin UY
Date:
Hi,

Is there any way for psql to output to the console AND file at the same time?
\o redirect the output to the file but at the same time it suppresses the output to the console :(

I am wanting to redirect the output to a file for the psql session for the SQL/SQL-script that I ran while at the same see the same output to screen.

Thought I can use tee -a but I can't :(
Using script [outputfile] seems to work. Just thought maybe there is any other psql trick besides using script. The script has lots of control characters :(

Thanks in advance.

Regards,
Edwin

Re: How to output psql to file AND screen/console

From
Erik Wienhold
Date:
On 2024-02-07 10:16 +0100, Edwin UY wrote:
> Is there any way for psql to output to the console AND file at the same
> time?
> \o redirect the output to the file but at the same time it suppresses the
> output to the console :(
> 
> I am wanting to redirect the output to a file for the psql session for the
> SQL/SQL-script that I ran while at the same see the same output to screen.
> 
> Thought I can use tee -a but I can't :(

Why not?  That was my first idea.

    $ psql | tee outfile

But the psql prompt is not printed on the console and tab completion via
readline does not work.  Maybe I'm missing something.

What does work is using \o and redirect to tee:

    $ psql finance
    psql (16.1)
    Type "help" for help.
    
    postgres=# \o | tee outfile
    postgres=# select now();
                  now              
    -------------------------------
     2024-02-07 11:08:23.207647+01
    (1 row)
    
    postgres=# \q
    
    $ cat outfile
                  now              
    -------------------------------
     2024-02-07 11:08:23.207647+01
    (1 row)

> Using script [outputfile] seems to work. Just thought maybe there is any
> other psql trick besides using script. The script has lots of control
> characters :(

-- 
Erik



Re: How to output psql to file AND screen/console

From
Edwin UY
Date:
Thanks Erik, the \o | tee outfile does the trick. Never thought of that.
It appears to exclude the output from psql I think like \conninfo or \encoding. 
Is there a way to get those to show in the outfile too? This is the same for when not using tee as well.

On Wed, Feb 7, 2024 at 11:11 PM Erik Wienhold <ewie@ewie.name> wrote:
On 2024-02-07 10:16 +0100, Edwin UY wrote:
> Is there any way for psql to output to the console AND file at the same
> time?
> \o redirect the output to the file but at the same time it suppresses the
> output to the console :(
>
> I am wanting to redirect the output to a file for the psql session for the
> SQL/SQL-script that I ran while at the same see the same output to screen.
>
> Thought I can use tee -a but I can't :(

Why not?  That was my first idea.

        $ psql | tee outfile

But the psql prompt is not printed on the console and tab completion via
readline does not work.  Maybe I'm missing something.

What does work is using \o and redirect to tee:

        $ psql finance
        psql (16.1)
        Type "help" for help.

        postgres=# \o | tee outfile
        postgres=# select now();
                      now             
        -------------------------------
         2024-02-07 11:08:23.207647+01
        (1 row)

        postgres=# \q

        $ cat outfile
                      now             
        -------------------------------
         2024-02-07 11:08:23.207647+01
        (1 row)

> Using script [outputfile] seems to work. Just thought maybe there is any
> other psql trick besides using script. The script has lots of control
> characters :(

--
Erik

Re: How to output psql to file AND screen/console

From
Erik Wienhold
Date:
On 2024-02-07 11:39 +0100, Edwin UY wrote:
> Thanks Erik, the \o | tee outfile does the trick. Never thought of that.
> It appears to exclude the output from psql I think like \conninfo or
> \encoding.

From the docs:

    '“Query results” includes all tables, command responses, and
     notices obtained from the database server, as well as output of
     various backslash commands that query the database (such as \d);
     but not error messages.'

https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-OUT

\conninfo and \encoding don't access the server, hence their outputs are
not query results.

> Is there a way to get those to show in the outfile too? This is the same
> for when not using tee as well.

Your first mail says that you want to run a script.  I don't see why
that shouldn't work:

    $ psql -f myscript.sql | tee outfile

In that case stdout will be piped to tee and that will also cover
output of commands such as \conninfo.

But I'm out of ideas for psql's interactive mode.

-- 
Erik



Re: How to output psql to file AND screen/console

From
Edwin UY
Date:
H Erik,

Thanks for noting that statement from the doc.

Sorry I didn't mention I am running the script as \i [sqlfile]

More often than not, I do a \conninfo first to confirm which database I am connecting to just to be safe. Wanting to redirect that information to the output file is for reference in case there's an issue and I needed to prove that I am indeed connected to the right database/host based on \conninfo.

At the moment, using script [outfile] and \o | tee [outfile]. The latter produces a cleaner output. Will search if there is a SQL that will give a similar output to \conninfo or do you happen to have one :-)


On Thu, Feb 8, 2024 at 1:52 AM Erik Wienhold <ewie@ewie.name> wrote:
On 2024-02-07 11:39 +0100, Edwin UY wrote:
> Thanks Erik, the \o | tee outfile does the trick. Never thought of that.
> It appears to exclude the output from psql I think like \conninfo or
> \encoding.

From the docs:

        '“Query results” includes all tables, command responses, and
         notices obtained from the database server, as well as output of
         various backslash commands that query the database (such as \d);
         but not error messages.'

https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-OUT

\conninfo and \encoding don't access the server, hence their outputs are
not query results.

> Is there a way to get those to show in the outfile too? This is the same
> for when not using tee as well.

Your first mail says that you want to run a script.  I don't see why
that shouldn't work:

        $ psql -f myscript.sql | tee outfile

In that case stdout will be piped to tee and that will also cover
output of commands such as \conninfo.

But I'm out of ideas for psql's interactive mode.

--
Erik

Re: How to output psql to file AND screen/console

From
Erik Wienhold
Date:
On 2024-02-07 20:13 +0100, Edwin UY wrote:
> Sorry I didn't mention I am running the script as \i [sqlfile]
> 
> More often than not, I do a \conninfo first to confirm which database I am
> connecting to just to be safe. Wanting to redirect that information to the
> output file is for reference in case there's an issue and I needed to prove
> that I am indeed connected to the right database/host based on \conninfo.

You can also use a wrapper script that runs \conninfo before prompting
the user on whether to continue with the actual script or abort.  For
example:

    \conninfo
    \prompt 'continue? [yes/no] ' continue
    \if :continue
        \echo 'continue'
        \unset continue
        \i :script
    \else
        \echo 'abort'
        \quit
    \endif

Run the wrapper by passing the script filename as variable :script:

    $ psql -v script=myscript.sql -f wrapper.sql | tee outfile

> At the moment, using script [outfile] and \o | tee [outfile]. The latter
> produces a cleaner output. Will search if there is a SQL that will give a
> similar output to \conninfo or do you happen to have one :-)

There's an active thread on -hackers about extending \conninfo.  There
you'll find a query[1] with functions that provide the same info.

[1] https://www.postgresql.org/message-id/20240206210605.GA3903769%40nathanxps13

-- 
Erik



Re: How to output psql to file AND screen/console

From
Edwin UY
Date:
Awesome, thanks for the info! 

On Thu, Feb 8, 2024 at 9:46 AM Erik Wienhold <ewie@ewie.name> wrote:
On 2024-02-07 20:13 +0100, Edwin UY wrote:
> Sorry I didn't mention I am running the script as \i [sqlfile]
>
> More often than not, I do a \conninfo first to confirm which database I am
> connecting to just to be safe. Wanting to redirect that information to the
> output file is for reference in case there's an issue and I needed to prove
> that I am indeed connected to the right database/host based on \conninfo.

You can also use a wrapper script that runs \conninfo before prompting
the user on whether to continue with the actual script or abort.  For
example:

        \conninfo
        \prompt 'continue? [yes/no] ' continue
        \if :continue
            \echo 'continue'
            \unset continue
            \i :script
        \else
            \echo 'abort'
            \quit
        \endif

Run the wrapper by passing the script filename as variable :script:

        $ psql -v script=myscript.sql -f wrapper.sql | tee outfile

> At the moment, using script [outfile] and \o | tee [outfile]. The latter
> produces a cleaner output. Will search if there is a SQL that will give a
> similar output to \conninfo or do you happen to have one :-)

There's an active thread on -hackers about extending \conninfo.  There
you'll find a query[1] with functions that provide the same info.

[1] https://www.postgresql.org/message-id/20240206210605.GA3903769%40nathanxps13

--
Erik