Thread: send psql output to file and setting pager=more

hello list.

these are just 2 very short questions.

is it possible in psql to ......
(1) i want it to stop after a screen of information.
i.e. like mysql's "[mysqld] pager=more .." my.cnf directive.

(2) also, i want to have the results show up on the screen and be tee-ed
to a file.  Oracle can do this, i don't remember if it's "set server
output on" or something like that?
i tried
\g out
but the file still had old information in it.
( \g defaults to cwd yes? )

thanks.
joesph.

On 5/20/06, kmh496 <kmh496@kornet.net> wrote:
> hello list.
>
> these are just 2 very short questions.
>
> is it possible in psql to ......
> (1) i want it to stop after a screen of information.
> i.e. like mysql's "[mysqld] pager=more .." my.cnf directive.
>
> (2) also, i want to have the results show up on the screen and be tee-ed
> to a file.  Oracle can do this, i don't remember if it's "set server
> output on" or something like that?
> i tried
> \g out
> but the file still had old information in it.
> ( \g defaults to cwd yes? )
What OS are you using postgres under?

Under Linux you can quite easily just run the command
non-interactively and actually paginate via less or more;

Alternatively, under psql (man psql for details :P) you can
use \pset pager less.

> thanks.
> joesph.
Cheers,
Andrej

> > is it possible in psql to ......
> > (1) i want it to stop after a screen of information.
> > i.e. like mysql's "[mysqld] pager=more .." my.cnf directive.
> >
> Under Linux you can quite easily just run the command
> non-interactively and actually paginate via less or more;
>
> Alternatively, under psql (man psql for details :P) you can
> use \pset pager less.
I put export PAGER=more
into my .bashrc and
executed the command you stated, as well as
\pset pager always
\pset pager more
but they don't stop after a page of input, so they seem to say they are
doing the correct thing but really aren't.

>
> > (2) also, i want to have the results show up on the screen and be
tee-ed
> > to a file.  Oracle can do this, i don't remember if it's "set server
> > output on" or something like that?

I learned the postgres equivalent is
\o filename
but this only directs STDOUT not STDERR
but, i want to capture the error messages into a file so I can then edit
that side by side with the other script which is generating the
messages.  Is it possible for \o to also capture output from both STDERR
and STDOUT?

On 5/20/06, kmh496 <kmh496@kornet.net> wrote:

You still didn't tell us the OS you're using, btw ...

> I put export PAGER=more
> into my .bashrc and
> executed the command you stated, as well as
Did you check whether it's actually set before running
psql?

> \pset pager always
> \pset pager more
> but they don't stop after a page of input, so they seem to say they are
> doing the correct thing but really aren't.
All I can say is it works here (Postgres 8.1.3 on Slackware 10.2).
Without PAGER set in bash I get more, if I export PAGER=less
that's what PostgreSQL uses.


> > > (2) also, i want to have the results show up on the screen and be
> tee-ed
> > > to a file.  Oracle can do this, i don't remember if it's "set server
> > > output on" or something like that?
>
> I learned the postgres equivalent is
> \o filename
> but this only directs STDOUT not STDERR
> but, i want to capture the error messages into a file so I can then edit
> that side by side with the other script which is generating the
> messages.  Is it possible for \o to also capture output from both STDERR
> and STDOUT?
That's where the non-interactive version comes in... (at least
I have no idea how to redirect errors to a file from the interactive
one):
echo "select * from table;"|psql database 2>&1|tee what.txt


Cheers,
Andrej


--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

I got angry and threw my computer out the window.  So, you don't need to
reply anymore.  Anyhow, let me tell you what happened.
> You still didn't tell us the OS you're using, btw ...
[postgres@www 2pg]$ cat /etc/redhat-release
Fedora Core release 4 (Stentz)
[postgres@www 2pg]$
[postgres@www 2pg]$
[postgres@www 2pg]$ psql --version
psql (PostgreSQL) 8.0.0beta3
contains support for command-line editing
[postgres@www 2pg]$
>
> > \pset pager always
> > \pset pager more
> > but they don't stop after a page of input, so they seem to say they are
> > doing the correct thing but really aren't.
> All I can say is it works here (Postgres 8.1.3 on Slackware 10.2).
> Without PAGER set in bash I get more, if I export PAGER=less
> that's what PostgreSQL uses.
i can't get it to work here.

>
>
mydb1=# \pset pager less
Pager is used for long output.
mydb1=# \o
mydb1=# \oset
mydb1=# \set
VERSION = 'PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC
gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
DBNAME = 'mydb1'
USER = 'postgres'
PORT = '5432'
ENCODING = 'UNICODE'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
LASTOID = '0'
mydb1=# \pset pager more
Pager usage is off.
mydb1=# \set
VERSION = 'PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC
gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
DBNAME = 'mydb1'
USER = 'postgres'
PORT = '5432'
ENCODING = 'UNICODE'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
LASTOID = '0'
mydb1=# \pset pager more
Pager is used for long output.
mydb1=# \set
VERSION = 'PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC
gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
DBNAME = 'mydb1'
USER = 'postgres'
PORT = '5432'
ENCODING = 'UNICODE'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
LASTOID = '0'
mydb1=# \pset pager
Pager usage is off.
mydb1=# \set
VERSION = 'PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC
gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
DBNAME = 'mydb1'
USER = 'postgres'
PORT = '5432'
ENCODING = 'UNICODE'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
LASTOID = '0'
mydb1=# \pset pager always
Pager is always used.
mydb1=# \set
VERSION = 'PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC
gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
DBNAME = 'mydb1'
USER = 'postgres'
PORT = '5432'
ENCODING = 'UNICODE'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
LASTOID = '0'
mydb1=# \pset pager less
Pager is used for long output.
mydb1=# \set
VERSION = 'PostgreSQL 8.0.0beta3 on i686-pc-linux-gnu, compiled by GCC
gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)'
AUTOCOMMIT = 'on'
VERBOSITY = 'default'
DBNAME = 'mydb1'
USER = 'postgres'
PORT = '5432'
ENCODING = 'UNICODE'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
LASTOID = '0'
mydb1=# \q
[postgres@www 2pg]$ echo $PAGER
more
[postgres@www 2pg]$ echo $SHELL
/bin/bash
[postgres@www 2pg]$ cat ~/.bashrc  | grep PAGER
export PAGER=more

> > > > (2) also, i want to have the results show up on the screen and be
> > tee-ed
> > > > to a file.  Oracle can do this, i don't remember if it's "set server
> > > > output on" or something like that?
> >
> > I learned the postgres equivalent is
> > \o filename
> > but this only directs STDOUT not STDERR
> > but, i want to capture the error messages into a file so I can then edit
> > that side by side with the other script which is generating the
> > messages.  Is it possible for \o to also capture output from both STDERR
> > and STDOUT?
> That's where the non-interactive version comes in... (at least
> I have no idea how to redirect errors to a file from the interactive
> one):
> echo "select * from table;"|psql database 2>&1|tee what.txt
>
>
> Cheers,
> Andrej
>
>
--
my site <a href="http://www.myowndictionary.com">myowndictionary</a> was
made to help students of many languages learn them faster.