PostgreSQL – Avoid Output Disappearing in psql

postgresqlpsql

I open psql and write:

mydb=> \dt

and this list of tables is shown:

                   List of relations
 Schema |            Name            | Type  |  Owner  
--------+----------------------------+-------+---------
 public | auth_group                 | table | foouser
 ...

The I have to press Esc to return to the prompt, and the output disappears like it was a less session.

I want to keep the output around so that I can use the information in it to write queries, but googling does not find an option to do that.

Best Answer

Assuming your pager is less, try LESS=-FX; export LESS in your shell environment.

From man less:

      -F or --quit-if-one-screen
              Causes less to automatically exit if the entire file can be dis‐
              played on the first screen.

      -X or --no-init
              Disables sending the termcap initialization and deinitialization
              strings to the terminal.  This is  sometimes  desirable  if  the
              deinitialization  string does something unnecessary, like clear‐
              ing the screen.

To have this only in psql, set it with a meta-command in .psqlrc:

\setenv LESS -FX

Related Solutions

Postgresql – psql won’t list databases

According to the pg_hba.conf snippet, a password is required if you connect from ::1, which is the IPv6 address for localhost.

It may be that the box on which you have the problem has the resolver configured so that the name "localhost" refers to both 127.0.0.1 (IPv4) and ::1 (IPv6), so that the command psql -h localhost... may connect to one or the other address.

I'd suggest to check the /etc/hosts file for different localhost entries, or bypass the resolver by connecting to 127.0.0.1, or update the pg_hba.conf with "trust" for IPv6 local connections.

Postgresql – Passing arguments to psql

Strictly speaking, there is no such thing as a "plpgsql script" - PL/pgSQL is the default procedural language of PostgreSQL. It's either an SQL script or a plpgsql function / procedure. Your example seems to indicate an SQL script.

You could create a (server-side ) plpgsql (or sql) function instead, that takes any number of arguments. It's very simple as long as the arguments are values. It gets a bit more complicated if the arguments include identifiers. Then you'll have to use PL/pgSQL with dynamic SQL and EXECUTE.

PL/pgSQL is pre-installed by default in PostgreSQL 9.0 or later. You have to install it once per database in Postgres 8.3, though:

CREATE LANGUGAGE plpgsql;

Speaking of the version: you should consider upgrading to a current version of PostgreSQL. v8.3 is very old by now, end-of-life in early 2013.

Since you seem to have a ready SQL script I'll demonstrate an SQL function. Simple dummy function with two integer arguments:

CREATE OR REPLACE FUNCTION func(int, int)
    LANGUAGE sql RETURNS void AS 
$func$
    UPDATE tbl1 SET col1 = $1 WHERE id = $2;
    UPDATE tbl2 SET col1 = $1 WHERE id = $2;
$func$;

You can find many more sophisticated examples for plpgsql here on dba.SE or on SO.

You can call this function and hand in parameters in a shell script: Basic example for a call in a shell script that uses input parameters for integer parameters (no single-quotes around the value needed):

psql mydb -c "SELECT func($1, $2)"

Or with any data type:

psql mydb -c "SELECT func2('$1'::text, '$2'::numeric)"

-c executes one command string and then exits. More about command line arguments of psql in the manual.

Best Answer

Related Solutions

Postgresql – psql won’t list databases

Postgresql – Passing arguments to psql

Related Question