Обсуждение: Graphing query results from within psql.
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.
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/
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
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