psql的备用输出格式

红太狼 2022-11-26 08:53 204阅读 0赞

本文翻译自：[Alternate output format for psql][]

*I am using PostgreSQL 8.4 on Ubuntu.* **我在Ubuntu上使用PostgreSQL 8.4。** *I have a table with columns `c1` through `cN` .* **我有一个表，其中的列`c1`到`cN` 。** *The columns are wide enough that selecting all columns causes a row of query results to wrap multiple times.* **列足够宽，选择所有列都会导致一行查询结果多次包装。** *Consequently, the output is hard to read.* **因此，输出难以读取。**

*When the query results constitute just a few rows, it would be convenient if I could view the query results such that each column of each row is on a separate line, eg* **当查询结果仅占几行时，如果我可以查看查询结果以使每一行的每一列都位于单独的行（例如，**

c1: <value of row 1's c1>
     c2: <value of row 1's c1>
     ...
     cN: <value of row 1's cN>
     ---- some kind of delimiter ----
     c1: <value of row 2's c1>
     etc.

*I am running these queries on a server where I would prefer not to install any additional software.* **我在不希望安装任何其他软件的服务器上运行这些查询。** *Is there a psql setting that will let me do something like that?* **是否有一个psql设置可以让我做类似的事情？**

--------------------

### \#1楼 ###

参考：[https://stackoom.com/question/EiCT/psql的备用输出格式][https_stackoom.com_question_EiCT_psql]

--------------------

### \#2楼 ###

*Also be sure to check out \\\\H, which toggles HTML output on/off.* **另外，请务必检查出\\\\ H，它会打开/关闭HTML输出。** *Not necessarily easy to read at the console, but interesting for dumping into a file (see \\\\o) or pasting into an editor/browser window for viewing, especially with multiple rows of relatively complex data.* **在控制台上不一定很容易阅读，但是对于转储到文件中（请参见\\\\ o）或粘贴到编辑器/浏览器窗口中进行查看（特别是包含多行相对复杂的数据）尤其有趣。**

--------------------

### \#3楼 ###

## *(New) Expanded Auto Mode: \\\\x auto* **（新）扩展的自动模式：\\\\ x自动** ##

*New for Postgresql 9.2;* **Postgresql 9.2的新增功能；** *PSQL automatically fits records to the width of the screen.* **PSQL自动使记录适合屏幕的宽度。** *previously you only had expanded mode on or off and had to switch between the modes as necessary.* **以前，您只能打开或关闭扩展模式，并且必须根据需要在模式之间进行切换。**

*  *If the record can fit into the width of the screen;* **记录是否适合屏幕宽度；** *psql uses normal formatting.* **psql使用常规格式。**
 *  *If the record can **not** fit into the width of the screen;* **如果记录不适合屏幕的宽度;** *psql uses expanded mode.* **psql使用扩展模式。**

*To get this use: **\\\\x auto*** **要使用此功能： \\\\ x auto**

*[Postgresql 9.5 Documentation on PSQL command.][]* **[PostgreSQL 9.5有关PSQL命令的文档。][Postgresql 9.5 Documentation on PSQL command.]**

--------------------

*Wide screen, normal formatting:* **宽屏，正常格式：**

id | time  |       humanize_time             | value 
    ----+-------+---------------------------------+-------
      1 | 09:30 |  Early Morning - (9.30 am)      |   570
      2 | 11:30 |  Late Morning - (11.30 am)      |   690
      3 | 13:30 |  Early Afternoon - (1.30pm)     |   810
      4 | 15:30 |  Late Afternoon - (3.30 pm)     |   930
    (4 rows)

--------------------

*Narrow screen, expanded formatting:* **窄屏，扩展格式：**

--------------------

### *How to start psql with \\\\x auto?* **如何使用\\\\ x auto启动psql？** ###

*Configure `\\x auto` command on startup by adding it to `.psqlrc` in your home folder and restarting psql.* **通过将`\\x auto`命令添加到主文件夹中的`.psqlrc`并重新启动psql，在启动时配置`\\x auto`命令。** *[Look under 'Files' section in the psql doc for more info][Postgresql 9.5 Documentation on PSQL command.] .* **[在psql doc中的“文件”部分下查找更多信息][Postgresql 9.5 Documentation on PSQL command.] 。**

***~/.psqlrc*** **〜/ .psqlrc**

\x auto

--------------------

### \#4楼 ###

*You have so many choices, how could you be confused :-)?* **您有很多选择，您怎么会困惑:-)？** *The main controls are:* **主要控件是：**

# \pset format
    # \H
    # \x
    # \pset pager off

*Each has options and interactions with the others.* **每个人都有选择和与其他人的互动。** *The most automatic options are:* **最自动的选项是：**

# \x off;\pset format wrapped
    # \x auto

*The newer "\\\\x auto" option switches to line-by-line display only "if needed".* **较新的“ \\\\ x自动”选项仅在“需要时”切换到逐行显示。**

-[ RECORD 1 ]---------------
    id          | 6
    description | This is a gallery of oilve oil brands.
    authority   | I love olive oil, and wanted to create a place for
    reviews and comments on various types.
    -[ RECORD 2 ]---------------
    id          | 19
    description | XXX Test A 
    authority   | Testing

*The older "\\\\pset format wrapped" is similar in that it tries to fit the data neatly on screen, but falls back to unaligned if the headers won't fit.* **较旧的“ \\\\ pset包装格式”与之类似，因为它试图将数据整齐地显示在屏幕上，但是如果标题不适合，则会退回到未对齐状态。** *Here's an example of wrapped:* **这是包装的示例：**

id |          description           |            authority            
    ----+--------------------------------+---------------------------------
      6 | This is a gallery of oilve     | I love olive oil, and wanted to
        ; oil brands.                    ;  create a place for reviews and
        ;                                ;  comments on various types.
     19 | Test Test A                    | Testing

--------------------

### \#5楼 ###

*One interesting thing is we can view the tables horizontally, without folding.* **一件有趣的事情是我们可以水平查看桌子，而无需折叠。** *we can use `PAGER` environment variable.* **我们可以使用`PAGER`环境变量。** *psql makes use of it.* **psql使用它。** *you can set* **你可以设置**

export PAGER='/usr/bin/less -S'

*or just `less -S` if its already availble in command line, if not with the proper location.* **如果在命令行中已经可用，则使用`less -S`或`less -S` （如果没有正确的位置）。** *-S to view unfolded lines.* **-S查看展开的线。** *you can pass in any custom viewer or other options with it.* **您可以将任何自定义查看器或其他选项传递给它。**

*I've written more in [Psql Horizontal Display][]* **我在[Psql水平显示中][Psql Horizontal Display]写了更多**

--------------------

### \#6楼 ###

*you can use the **zenity** to displays the query output as html table.* **您可以使用zenity将查询输出显示为html表。**

*  *first implement bash script with following code:* **首先用以下代码实现bash脚本：**
    
    ***cat > '/tmp/sql.op';*** **cat>'/tmp/sql.op';** ***zenity --text-info --html --filename='/tmp/sql.op';*** **zenity --text-info --html --filename ='/ tmp / sql.op';**
    
    *save it like **mypager.sh*** ***像mypager.sh一样保存***
 *  *Then export the environment variable PAGER by set full path of the script as value.* **然后通过将脚本的完整路径设置为值来导出环境变量PAGER。**
    
    *for example:- **export PAGER='/path/mypager.sh'*** **例如： \-export PAGER ='/ path / mypager.sh'**
 *  *Then login to the psql program then execute the command **\\\\H*** **然后登录到psql程序，然后执行命令\\\\ H**
 *  *And finally execute any query,the tabled output will displayed in the zenity in html table format.* **最后执行任何查询，表格输出将以zenity格式以html表格格式显示。**

[Alternate output format for psql]: https://oldbug.net/q/EiCT/Alternate-output-format-for-psql
[https_stackoom.com_question_EiCT_psql]: https://stackoom.com/question/EiCT/psql%E7%9A%84%E5%A4%87%E7%94%A8%E8%BE%93%E5%87%BA%E6%A0%BC%E5%BC%8F
[Postgresql 9.5 Documentation on PSQL command.]: https://www.postgresql.org/docs/9.5/static/app-psql.html
[Psql Horizontal Display]: https://learnersguide.wordpress.com/2015/11/25/psql-horizontal-table-display/