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

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

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

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