PSQL基本使用(定製PSQL提示符)

環境描述:

作業系統:            CentOS Linux release 7.6.1810 (Core) 

postgresql版本       postgres (PostgreSQL) 12.4

host1               192.168.112.200

host2               192.168.112.201

首先翻看 postgresql(12.4) 官方文件 (Part VI.Reference => PostgreSQL Client Applications =>psql) 的 Prompting 部分描述

也可以參考 postgresql(12.4) 的原始碼 src/bin/psql/prompt.c  檔案中 get_prompt 的註釋部分

Prompting

The prompts psql issues can be customized to your preference. The three variables PROMPT1,PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when psql requests a new command. Prompt 2 is issued when more input is expected during command entry, for example because the command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.The value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are:

%M

The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location.

%m

The host name of the database server, truncated at the first dot, or [local] if the connection is over a Unix domain socket.

%>

The port number at which the database server is listening.

%n

The database session user name. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)

%/

The name of the current database.

%~

Like %/, but the output is ~ (tilde) if the database is your default database.

%#

If the session user is a database superuser, then a #, otherwise a >. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)

%p

The process ID of the backend currently connected to.

%R

In prompt 1 normally =, but @ if the session is in an inactive branch of a conditional block, or ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails). In prompt 2 %R is replaced by a character that depends on why psql expects more input: - if the command simply wasn't terminated yet, but * if there is an unfinished / * ... */ comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string,or ( if there is an unmatched left parenthesis. In prompt 3 %R doesn't produce anything.

%x

Transaction status: an empty string when not in a transaction block, or * when in a transaction block, or ! when in a failed transaction block, or ? when the transaction state is indeterminate (for example, because there is no connection).

%l

The line number inside the current statement, starting from 1.%digits The character with the indicated octal code is substituted.

%:name:

The value of the psql variable name. See the section Variables for details.

%`command`

The output of command, similar to ordinary “back-tick” substitution.

%[ ... %]

Prompts can contain terminal control characters which, for example, change the color,background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these can occur

within the prompt. For example:

testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '

results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, colorcapable terminals.

To insert a percent sign into your prompt, write %%. The default prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.

首先PROMPT1\PROMPT2\PROMPT3 的區別:

 Prompt 1 is the normal prompt that is issued when psql requests a new command

 Prompt 2 is issued when more input is expected during command entry

 Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.

PROMPT 的預設值: 

  The default prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.

我們通過實驗進行一下測試.

  

 Prompt 1:

   [postgres@pg1 ~]$ psql -h 192.168.112.201

   psql (12.4)

   Type "help" for help.

   postgres=# \set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x '     <=  配置前

   192.168.112.201:5432 postgres@postgres=#                                  <=  配置後

 Prompt 2:

    配置前:

       postgres=# select *

       postgres-# from

       postgres-# test

       postgres-# ;

    配置後:

       postgres=# \set PROMPT2 '%M %n@%/%R %# '

       postgres=# select *

       192.168.112.201 postgres@postgres- # from 

       192.168.112.201 postgres@postgres- # test

       192.168.112.201 postgres@postgres- # ;

 Prompt 3:

    配置前:

       postgres=# copy test from stdin;

       Enter data to be copied followed by a newline.

       End with a backslash and a period on a line by itself, or an EOF signal.

       >> 1

       >> 2

       >> 3

       >> \.

       COPY 3

    配置後:

       postgres=# \set PROMPT3 '%M %n@%/%R %# '

       postgres=# copy test from stdin;

       Enter data to be copied followed by a newline.

       End with a backslash and a period on a line by itself, or an EOF signal.

       192.168.112.200 postgres@postgres # 1

       192.168.112.200 postgres@postgres # 2

       192.168.112.200 postgres@postgres # 3

       192.168.112.200 postgres@postgres # \.

       COPY 3

帶日期等個性化配置:

       [postgres@pg1 ~]$ psql -h 192.168.112.201

       psql (12.4)

       Type "help" for help.

       postgres=# \set PROMPT1 '%`date +%H:%M:%S` (%n@%M:%>)%/%R%#%x ' 

       21:13:55 (postgres@192.168.112.201:5432)postgres=# 

配置固化到配置檔案:

1.在/home/postgres 下建立 .psqlrc 檔案.

\set PROMPT1 '%`date +%H:%M:%S` (%n@%M:%>)%/%R%#%x ' 

\set PROMPT2 '%M %n@%/%R %# '

\set PROMPT3 '%M %n@%/%R %# '

2.根據postgresql原始碼檔案 src/bin/psql/psqlrc.sample  檔案內容

--

-- system-wide psql configuration file

--

--  This file is read before the .psqlrc file in the user's home directory.

--

--  Copy this to your installation's sysconf directory and rename it psqlrc.

--  The sysconf directory can be identified via "pg_config --sysconfdir".

[postgres@pg1 ~]$ pg_config --sysconfdir

/pgsoft/etc/postgresql

可見 /pgsoft/etc/postgresql  目錄下的 psqlrc 檔案是優先於  .psqlrc 進行讀取的.