Re: Database INNOVATION - Mailing list pgsql-general

Gotcha.

Apologies for the digression, off your exact topic but consistent with the subject :-)

I'm interested in both, PL/R & representational graphics from an analytical perspective, doing more than just retrieving raw or accumulated data with SQL. & also from the (mathemetical) graphic perspective to support biological taxonomic trees/heirarchies, which do not easily fit the SQL model, although a number of kludges to traverse such structures are around.

(I need to look at the Postgres recursive capability for this sometime)

Cheers,

  Brent



>>> Craig Ringer <craig@postnewspapers.com.au> 10/20/10 6:12 PM >>>
On 10/20/2010 12:35 PM, Brent Wood wrote:
> Have a look at PL/R.
>
> You can embed a command to generate a graphic using R via a user defined
> SQL function,

In this case, when I say "graph" or "tree" I'm referring to the concept
in the graph theory sense, not the "plot" sense. "object graph" not
"image representation of data".

http://en.wikipedia.org/wiki/Graph_(mathematics)
http://en.wikipedia.org/wiki/Graph_theory

Sorry, I didn't even think to clarify my usage.

What I'm talking about is a way to query the database and obtain a
representation of matching tuples where each tuple is represented
exactly once, and referential relationships between tuples are included
in an efficient way.

For a simple tree or forest (ie a directed graph with no cycles) this
could be a XML/JSON/YAML/whatever document that uses nesting to
represent relationships.

For more complex graphs, it'd have to be a list of
XML/JSON/YAML/whatever representations of each tuple or (if Pg supported
it) multiple tabular result sets, one for each tuple type. An edge list
could be included to speed mapping out the inter-object references after
deserialization.

To say this would be nice when dealing with document-in-database storage
and certain types of ORM workload is quite an understatement. Getting
rid of all that horrid "multiply left join, filter and de-duplicate" or
"n+1 select" crap would be quite lovely. Sure, it's often better to use
sane SQL directly, but there are tasks for which ORMs or
document-database mappings are a real time and pain saver - it'd just be
nice to be able to teach the database their language.

Plus, that'd help shut up the "NoSQL" crowd and separate "NoSQL" from
"relaxed or no ACID shareded databases", two different things people
currently confuse.

In any case, thanks for the tip. It's nice to know the PL/R can be used
for such in-database processing when I *do* want to plot data.

--
Craig Ringer