On: How I work with Postgres – psql, My PostgreSQL Admin

[atsaloli]

Here is an example of feeding query output into gnuplot without leaving psql:

  # psql -U postgres
  psql (8.4.15)
  Type "help" for help.

  postgres=# \t
  Showing only tuples.
  postgres=# \a
  Output format is unaligned.
  postgres=# \f ' '
  Field separator is " ".
  postgres=# select * from example;
  1 1
  2 2
  3 3
  4 4
  postgres=# \o | /usr/bin/gnuplot
  postgres=# select 'set title "My Graph"; set terminal dumb 78 24; set key off; set ylabel "Time"; set xlabel "Servers";' || 'plot ''-'' with lines;' ; select * from example;
  postgres=# \o

                                    My Graph
  Time
      4 ++----------+----------+-----------+----------+-----------+---------**
        +           +          +           +          +            +     **** +
      |                                                           ****     |
  3.5 ++                                                      ****        ++
      |                                                   ****             |
      |                                               ****                 |
    3 ++                                           ***                    ++
      |                                        ****                        |
      |                                    ****                            |
  2.5 ++                               ****                               ++
      |                            ****                                    |
      |                        ****                                        |
    2 ++                    ***                                           ++
      |                 ****                                               |
      |             ****                                                   |
  1.5 ++        ****                                                      ++
      |     ****                                                           |
      + ****      +          +           +          +           +          +
    1 **----------+----------+-----------+----------+-----------+---------++
      1          1.5         2          2.5         3          3.5         4
                                     Servers

  postgres=#

[fusiongyro → atsaloli]

This is incredible! I only wish it were a little easier to do on the fly.

[atsaloli → fusiongyro]

Yeah, gnuplot is a very powerful tool.

I imagine you could put it all into a user-defined postgresql function, so all you have to say is:

   graph(select * from example);

[atombender → atsaloli]

You should be able to map it to a macro using \set.

    # \set foo '(select now())'
    # :foo;
    ?column?
    --------
    2013-02-14 04:18:23+01

    # select count(*) from :foo as foo;
    count
    -----
    

Afaik macros just expand inline, do you can embed stuff like \o.

[atsaloli → atombender]

Aye. This is getting over my head, so I inquired on the pgsql-general list (which is amazingly helpful).

Ian Barwick writes how to put all the prep stuff into a psql script, then all you have to do is define your query and invoke the script:

-- start quote --

What you could do is create a small psql script along these lines:

  barwick@localhost:~$ cat tmp/plot.psql
  \set QUIET yes
  \t\a\f ' '
  \unset QUIET
  \o | /usr/bin/gnuplot
  select 'set title "My Graph"; set terminal dumb 78 24; set key off; set ylabel "Time"; set xlabel "Servers";' || 'plot ''-'' with lines;' ;
  :plot_query;
  \set QUIET yes
  \t\a\f
  \unset QUIET
  \o

  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=#

-- end quote --

And

Sergey Konoplev explains how to do it with a server-side function - you need gnuplot installed on the db server -

-- start quote --

  plpython/plperl/etc plus this way of calling

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

  will do the trick.

-- end quote --