Graphing within psql

I mentioned this on HN years ago but it’s nifty so add it here.

You can graph SQL output with gnuplot without leaving the psql (Postgres client) command-line.

Because @fusiongyro commented “This is incredible! I only wish it were a little easier to do on the fly,” I inquired on the amazingly helpful pgsql-general list.

There are two approaches: client-side and server-side.

• Ian Barwick explained how to put all the prep stuff into a psql script, define your query and invoke the script.

  
  barwick@localhost:~$ psql -U postgres testdb
    psql (9.2.3)
    Type "help" for help.
  
    testdb=# set plot_query 'SELECT * FROM plot'
    testdb=# i tmp/plot.psql
  
  
                                      My Graph
  
      4 ++---------+-----------+----------+----------+-----------+---------**
        +          +           +          +          +           +     **** +
        |                                                          ****     |
    3.5 ++                                                     ****        ++
        |                                                  ****             |
        |                                              ****                 |
      3 ++                                         ****                    ++
        |                                      ****                         |
    2.5 ++                                *****                            ++
        |                             ****                                  |
        |                         ****                                      |
      2 ++                    ****                                         ++
        |                 ****                                              |
        |             ****                                                  |
    1.5 ++        ****                                                     ++
        |     ****                                                          |
        + ****     +           +          +          +           +          +
      1 **---------+-----------+----------+----------+-----------+---------++
        1         1.5          2         2.5         3          3.5         4
                                       Servers
  
    testdb=#
  

• Sergey Konoplev explained how to do it with a server-side function – you need gnuplot installed on the db server and then you can use

select just_for_fun_graph('select ... from ...', 'My Graph', 78, 24, ...)