Re: Graphing query results from within psql.

2013/2/14 Aleksey Tsalolikhin <atsaloli.tech@gmail.com>:
> Below is an example of feeding query output to gnuplot without leaving
psql.
> I'd like to call it as "select graph(select * from example)", just for
fun.
> What do I need to learn to do that, please?  Can I create a function that
> uses "\o"?  I think not, because a function runs server-side and \o is a
> client side feature.

You are correct, it is not possible for a backend function to interact
directly with psql. You'd need to create a function in PL/Perl etc.,
and would have to have gnuplot available on the DB server.

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

HTH

Ian Barwick