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

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

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);

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.

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

You could configure the PAGER environment variable and "\pset pager always" so that all your query results are passed through an external script. The external script would have to decide whether to just spit it out on stdout to display as usual, or to send the query output to a graphing program. This is a pretty ugly solution but I'd be interested to know if anyone is doing anything like this.

I found that the best way to do this is to use the RPostgreSQL/R*SQL driver and plot the graph through R.

Heres a little more on it:

http://theexceptioncatcher.com/blog/2012/11/really-cool-thin...

And a question related to it: http://stackoverflow.com/questions/13295855/plotting-multipl...

We have graphs in the plan for CartoDB development, and it is built on postgresql, so hopefully making it easier for people to start getting graphs out of their databses.