Archive for December, 2010
Some say SQL is not good at graphics.
Well, they have some point. Database engines lack scanner drivers, there is no easy way to do sepia, and magic wand, let’s be honest, is just poor.
However, you can make some New Year paintings with SQL.
Let’s make a New Year clock showing 12 o’clock in Oracle.
Answering questions asked on the site.
Jan Suchal asks:
We’ve started playing with PostgreSQL and recursive queries. Looking at example that does basic graph traversal from http://www.postgresql.org/docs/9.0/static/queries-with.html.
We would like to have a view called
pathsdefined like this:WITH RECURSIVE search_graph(id, path) AS ( SELECT id, ARRAY[id] FROM node UNION ALL SELECT g.dest, sg.path || g.dest FROM search_graph sg JOIN graph g ON g.source = sg.id AND NOT g.dest = ANY(sg.path) ) SELECT path FROM search_graph
By callingSELECT * FROM paths WHERE path = :node_id
we would get all paths from a certain node.
The problem here is with performance. When you want this to be quick you need to add a condition for the anchor part of the
UNIONlike this:WITH RECURSIVE search_graph(id, path) AS ( SELECT id, ARRAY[id] FROM node WHERE id = :node_id UNION ALL SELECT g.dest, sg.path || g.dest FROM search_graph sg JOIN graph g ON g.source = sg.id AND NOT g.dest = ANY(sg.path) ) SELECT path FROM search_graph
Now it’s perfectly fast, but we cannot create a view because that would only contain paths from one specific node.
An often overlooked feature of PostgreSQL is its ability to create set-returning functions and use them in
The record will be cross-joined with the set returned by the function and the result of the join will be added to the resultset.
This is best demonstrated with
generate_series, probably a most used PostgreSQL set-returning function.
Read the rest of this entry »