Re: Database INNOVATION - Mailing list pgsql-general

Have a look at PL/R.

You can embed a command to generate a graphic using R via a user defined SQL function,

This example from http://www.varlena.com/GeneralBits/Tidbits/bernier/art_66/graphingWithR.html

HTH

  Brent Wood

=====================================================================================

Graphs can be as easy as '123'. Here's an example where two columns in a table are plotted against each other.

Create and populate the table using the following commands:

CREATE TABLE temp (x int, y int);

INSERT INTO temp VALUES(4,6);

INSERT INTO temp VALUES(8,3);

INSERT INTO temp VALUES(4,7);

INSERT INTO temp VALUES(1,5);

INSERT INTO temp VALUES(7,8);

INSERT INTO temp VALUES(2,3);

INSERT INTO temp VALUES(5,1);

INSERT INTO temp VALUES(9,4);

The function f_graph() generates the graph as a pdf document:

CREATE OR REPLACE FUNCTION f_graph() RETURNS text AS
'

str <<- pg.spi.exec (''select x as "my a" ,y as "my b" from temp order by x,y'');

pdf(''/tmp/myplot.pdf'');

plot(str,type="l",main="Graphics Demonstration",sub="Line Graph");

dev.off();

print(''done'');

'
LANGUAGE plr;

Creating the graph by invoking this query:

SELECT f_graph();  

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Craig Ringer said:

Now, personally, if we're talking "database innovation" what I'd like to
see is a built-in way to get query results straight from the database as
graphs of tuples and their relationships. Tabular result sets are poorly
suited to some kinds of workloads, including a few increasingly common
ones like document-oriented storage and use via ORMs. In particular, the
way ORMs tend to do multiple LEFT OUTER JOINs and deduplicate the
results or do multiple queries and post-process to form a graph is
wasteful and slow. If Pg had a way to output an object graph (or at
least tree) natively as, say, JSON, that'd be a marvellous option for
some kinds of workloads, and might help the NoSQL folks from whining
quite so much as well ;-)



--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

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