2014-05-20

How to draw a tree using window functions and recursive query in PostgreSQL

In this post I would like to show you how to draw a nice looking tree (similar to output of unix tree command) using select statement in PostgreSQL.

Suppose we have a view v_deps, which contains information about dependencies between views, materialized views and tables (the definition of this view is at the end of the post). Every row in this view means that dependent object dep_obj depends on referenced object ref_obj: