Выполнение команд SQL и форматирование результатов

Запускаем psql:

postgres$ psql 

Проверим текущее подключение:

=> \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".

Команда \c[onnect] выполняет новое подключение, не покидая psql.


psql умеет выводить результат запросов в разных форматах:


Формат с выравниванием используется по умолчанию:

=> SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;
 schemaname |    tablename    | tableowner 
------------+-----------------+------------
 pg_catalog | pg_statistic    | postgres
 pg_catalog | pg_user_mapping | postgres
 pg_catalog | pg_policy       | postgres
 pg_catalog | pg_authid       | postgres
 pg_catalog | pg_subscription | postgres
(5 rows)

Ширина столбцов выровнена по значениям. Также выводится строка заголовков и итоговая строка.


Команды psql для переключения режима выравнивания:


Отключим выравнивание, вывод заголовка и итоговой строки, а в качестве разделителя столбцов сделаем пробел:

=> \t \a
Tuples only is on.
Output format is unaligned.
=> \pset fieldsep ' '
Field separator is " ".
=> SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;
pg_catalog pg_statistic postgres
pg_catalog pg_user_mapping postgres
pg_catalog pg_policy postgres
pg_catalog pg_authid postgres
pg_catalog pg_subscription postgres
=> \t \a
Tuples only is off.
Output format is aligned.

Расширенный формат удобен, когда нужно вывести много столбцов для одной или нескольких записей:

=> \x
Expanded display is on.
=> SELECT * FROM pg_tables WHERE tablename = 'pg_class';
-[ RECORD 1 ]-----------
schemaname  | pg_catalog
tablename   | pg_class
tableowner  | postgres
tablespace  | 
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f

=> \x
Expanded display is off.

Расширенный режим можно установить только для одного запроса, если в конце вместо ";" указать \gx:

=> SELECT * FROM pg_tables WHERE tablename = 'pg_proc' \gx
-[ RECORD 1 ]-----------
schemaname  | pg_catalog
tablename   | pg_proc
tableowner  | postgres
tablespace  | 
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f

Все возможности форматирования результатов запросов доступны через команду \pset.


Взаимодействие с ОС

Можно выполнять команды shell:

=> \! pwd
/home/postgres
=> \! uptime
 17:29:32 up  4:36,  3 users,  load average: 0,97, 0,57, 0,56

Можно установить переменную окружения:

=> \setenv TEST Hello
=> \! echo $TEST
Hello

Можно записать вывод команды в файл с помощью \o[ut]:

=> \o dba1_log
=> SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;

На экран ничего не попало. Посмотрим в файле:

=> \! cat dba1_log
 schemaname |    tablename    | tableowner 
------------+-----------------+------------
 pg_catalog | pg_statistic    | postgres
 pg_catalog | pg_user_mapping | postgres
 pg_catalog | pg_policy       | postgres
 pg_catalog | pg_authid       | postgres
 pg_catalog | pg_subscription | postgres
(5 rows)

Вернем вывод на экран:

=> \o

Выполнение скриптов

Запишем в файл команды SQL.

=> \a \t
Output format is unaligned.
Tuples only is on.
=> \pset fieldsep ' '
Field separator is " ".
=> \o dba1_log
=> SELECT 'SELECT '''||tablename||': ''|| count(*) FROM '||tablename||';' 
   FROM pg_tables LIMIT 3;
=> \o

Вот что получилось в файле:

=> \! cat dba1_log
SELECT 'pg_statistic: '|| count(*) FROM pg_statistic;
SELECT 'pg_user_mapping: '|| count(*) FROM pg_user_mapping;
SELECT 'pg_policy: '|| count(*) FROM pg_policy;

И выполним теперь эти команды с помощью \i[nclude]:

=> \i dba1_log
pg_statistic: 393
pg_user_mapping: 0
pg_policy: 0

Другие способы выполнить команды из файла:


В предыдущем примере можно обойтись и без создания файла, если завершить запрос командой \gexec:

=> SELECT 'SELECT '''||tablename||': ''|| count(*) FROM '||tablename||';' 
   FROM pg_tables LIMIT 3 \gexec
pg_statistic: 393
pg_user_mapping: 0
pg_policy: 0

Значение каждого столбца каждой строки выполняется отдельной командой SQL.

Восстановим форматирование по умолчанию.

=> \t \a
Tuples only is off.
Output format is aligned.

Переменные psql и управляющие конструкции

По аналогии с shell, psql имеет собственные переменные, среди которых есть ряд встроенных (имеющих определенный смысл для psql).

Установим переменную:

=> \set TEST Hi!

Чтобы получить значение, надо предварить имя переменной двоеточием:

=> \echo :TEST
Hi!

Значение переменной можно сбросить:

=> \unset TEST
=> \echo :TEST
:TEST

Можно результат запроса записать в переменную. Для этого запрос нужно завершить командой \gset вместо ";":

=> SELECT now() AS curr_time \gset
=> \echo :curr_time
2018-12-24 17:29:32.833176+03

Запрос должен возвращать только одну запись.


Без параметров \set выдает значения всех переменных:

=> \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'postgres'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
FETCH_COUNT = '0'
HISTCONTROL = 'none'
HISTFILE = 'hist'
HISTSIZE = '500'
HOST = '/tmp'
IGNOREEOF = '0'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '10.0'
SERVER_VERSION_NUM = '100000'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'postgres'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 10.0 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.5) 5.4.0 20160609, 32-bit'
VERSION_NAME = '10.0'
VERSION_NUM = '100000'
curr_time = '2018-12-24 17:29:32.833176+03'

В командных файлах можно использовать условные операторы.

Например, требуется найти размер каталога с WAL-сегментами. Начиная с версии 10 WAL-сегменты располагаются в каталоге pg_wal, в более ранних версиях они располагались в pg_xlog.

Сформируем переменную pgsql10, значение которой говорит о том, является ли версия сервера 10 или новее:

=> SELECT :SERVER_VERSION_NUM >= 100000 AS pgsql10\gset
=> \echo pgsql10: :pgsql10
pgsql10: t

Для определения имени каталога используем логическую переменную в условном операторе:

=> \if :pgsql10
   \! echo "WAL size:";du -h -s $PGDATA/pg_wal
\else
   \! echo "WAL size:";du -h -s $PGDATA/pg_xlog
\endif
WAL size:
17M	/usr/local/pgsql/data/pg_wal

Команды для работы с системным каталогом

С помощью серии команд, в основном начинающихся на \d, можно смотреть информацию об объектах БД.

Например:

=> \d pg_tables
              View "pg_catalog.pg_tables"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 schemaname  | name    |           |          | 
 tablename   | name    |           |          | 
 tableowner  | name    |           |          | 
 tablespace  | name    |           |          | 
 hasindexes  | boolean |           |          | 
 hasrules    | boolean |           |          | 
 hastriggers | boolean |           |          | 
 rowsecurity | boolean |           |          | 

Подробнее эти команды будут рассмотрены позже.


Настройка psql

Файл .psqlrc из домашнего каталога пользователя выполняется при запуске psql. В него можно поместить команды для настройки сеанса работы. Среди них:


Например, вот определение переменной top5 с текстом запроса на получение пяти самых больших по размеру таблиц:

=> \set top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) AS bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'

Для выполнения запроса достаточно набрать:

=> :top5
   tablename    |  bytes  
----------------+---------
 pg_depend      | 1040384
 pg_proc        |  974848
 pg_rewrite     |  630784
 pg_attribute   |  598016
 pg_description |  491520
(5 rows)

Если записать эту команду \set в .psqlrc, то переменная top5 будет всегда доступна.


Благодаря поддержке readline, в psql работает автодополнение ключевых слов и имен объектов, а также сохраняется история команд. Имя и размер файла истории настраиваются переменными HISTFILE, HISTSIZE.


Конец демонстрации.