EXPLAIN EXTENDED

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.

Read the rest of this entry »

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 paths defined like this:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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 calling

?

1 2 3

SELECT  * FROM    paths WHERE   path[1] = :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 UNION like this:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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.

Any ideas?

An often overlooked feature of PostgreSQL is its ability to create set-returning functions and use them in SELECT list.

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 »