Graphing query results from within psql.

On Wed, Feb 13, 2013 at 5:17 PM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

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.

plpython/plperl/etc plus this way of calling

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

will do the trick.

This is not a production issue, just learning, for fun.

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

Best,
Aleksey

--
CFEngine Trainings:
Los Angeles, Feb 25 - 28. http://cf3la.eventbrite.com
New Jersey, Apr 29 - May 2. http://cf3.eventbrite.com/

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

Great, now I have two ways to do it, front and back. Thanks, Sergey and Ian!

Best,
Aleksey