pg_show_deps: outputs dependencies graph for tables, types, proc, views in PostgreSQL database schema. (See related posts)

pg_show_deps: outputs dependencies graph for tables, types, proc, views in PostgreSQL database schema.
output is produced in dot format and can be processed using graphviz (http://www.graphviz.org/) toolset
# to get a picture of dependencies.

#!/bin/env ruby
#
# pg_show_deps: outputs dependencies graph for objects (tables, types, proc, views) in PostgreSQL database schema.
# output is produced in dot format and can be processed using graphviz (http://www.graphviz.org/) toolset
# to get a picture of dependencies.
#
# Sample usage:
#    pg_show_deps 'dbname=template1 port=5432 user=looser' >template1.dot
#    ccomps -otemplate_split.dot -x template1.dot
#    dot -Tps -o template_2.ps template_split_2.dot
#
# Developed using PostgreSQL 8.1, 
# requires ruby-postgres driver (http://rubyforge.org/projects/ruby-postgres)

require 'postgres'
$PG_CLASSES = {}
            
class DBObject
  attr_accessor :row, :o_type, :nsp
  def initialize(conn, class_id, obj_id, sub_id)
    @o_type   = $PG_CLASSES[class_id]
    @row   = conn.query(sql_for(@o_type, obj_id, sub_id)).first
    @nsp = row['nsp']
    if @o_type == 'pg_proc'
      arg_types = row.last.split(" ")
      unless arg_types.empty?
        arg_type_names = arg_types.map {|oid| "format_type(#{oid}, -1)"}.join(", ")
        row[-1] = "("+conn.query("SELECT #{arg_type_names}").first.join(", ") +")"
      else 
	     row[-1] = "()"
      end
    end
  end
  def sql_for(pg_class, obj_id, sub_id)
  base_sql = case pg_class 
    when 'pg_type' 
      "select       (case 
      	when typtype = 'b' then 'BASE '
	when typtype = 'c' then 'COMPOSITE '
	when typtype = 'd' then 'DOMAIN '
	when typtype = 'p' then 'PSEUDO '
      end) || coalesce( 
      (select (CASE WHEN relkind = 'r' THEN 'TABLE'
            WHEN relkind = 'v' THEN 'VIEW'
            WHEN relkind = 'i' THEN 'INDEX'
            WHEN relkind = 'S' THEN 'SEQUENCE'
            WHEN relkind = 's' THEN 'SPECIAL'
            WHEN relkind = 't' THEN 'TOAST'
	    WHEN relkind = 'c' THEN ' '
        END) from pg_class c where c.oid = typrelid), ' '), 
(select nspname from pg_namespace n where n.oid = typnamespace) as nsp,       typname as obj_name,
      #{sub_id} from pg_type  "
    when 'pg_proc' 
      "select (select nspname from pg_namespace n where n.oid = pronamespace) as nsp, proname as obj_name, proargtypes from pg_proc "
    when 'pg_class'
      "select 
  (CASE WHEN relkind = 'r' THEN 'TABLE'
            WHEN relkind = 'v' THEN 'VIEW'
            WHEN relkind = 'i' THEN 'INDEX'
            WHEN relkind = 'S' THEN 'SEQUENCE'
            WHEN relkind = 's' THEN 'SPECIAL'
            WHEN relkind = 't' THEN 'TOAST'
        END) , (select nspname from pg_namespace n where n.oid = relnamespace) as nsp, 
       relname as obj_name,  
      (select attname from pg_attribute where attrelid = #{obj_id} and attnum = #{sub_id}) from pg_class"
    when 'pg_rewrite'
      "select (select nspname from pg_namespace n where n.oid = (select relnamespace from pg_class c where c.oid = ev_class) ) as nsp, rulename, (select relname from pg_class c where c.oid = ev_class)  from pg_rewrite"
    else nil
  end
  base_sql = base_sql ? base_sql << " WHERE oid = #{obj_id} ": nil
  
end
 
  def to_s
    case @o_type
    	when 'pg_proc' : "FUNCTION\\n#{@row[0]}.#{@row[1]}#{@row[2]}"
	when 'pg_type': "TYPE: #{@row[0]}\\n#{@row[1]}.#{@row[2]}"
	when 'pg_class' : "#{@row[0]}\\n#{@row[1]}.#{@row[2]}" + (row[3] ? "\\nCOLUMN #{@row[3]}" : "")
	when 'pg_rewrite' :  @row[1] == '_RETURN' ? "VIEW\\n#{@row[0]}.#{@row[2]}" : "RULE #{@row[1]} ON \\n#{@row[0]}.#{@row[2]}"
    end 
  end
end

# connection to database
conn=PGconn.new(ARGV[0])

conn.query("select distinct classid, relname from pg_class c join pg_depend d on (c.oid = d.classid)").each do |row|
  $PG_CLASSES[row['classid']] = row['relname']
end
conn.query("select distinct refclassid, relname from pg_class c join pg_depend d on (c.oid = d.refclassid)").each do |row|
  $PG_CLASSES[row['refclassid']] = row['relname']
end
ACCEPTED = ['pg_type', 'pg_proc', 'pg_class', 'pg_rewrite']
dep_graphs = {}
conn.query("SELECT * FROM pg_catalog.pg_depend   where   deptype<> 'i' and deptype <> 'p'").each do |row|
  if ACCEPTED.include?($PG_CLASSES[row['classid']]) && ACCEPTED.include?($PG_CLASSES[row['refclassid']])
    this_obj = DBObject.new(conn, row['classid'], row['objid'], row['objsubid'])
    that_obj = DBObject.new(conn, row['refclassid'], row['refobjid'], row['refobjsubid'])
    #dep_string = this_obj.nsp !~ /^pg_toast$/ ? "\"#{this_obj.to_s}\" -> \"#{that_obj.to_s}\"; \n" : nil
    dep_string = this_obj.nsp !~ /^(information_schema|pg_catalog|pg_toast)$/  ? "\"#{this_obj.to_s}\" -> \"#{that_obj.to_s}\"; \n" : nil
    
    if dep_string && this_obj.to_s !~ /^INDEX\\n/
      dep_graphs[that_obj.to_s] ||=[]
      dep_graphs[that_obj.to_s] << dep_string
      if that_obj.to_s =~ /^(VIEW|TABLE)\\n(.+?)\\nCOLUMN /m
        ds2 = that_obj.to_s.sub(/\\nCOLUMN.*/m, '')
        dep_graphs[ds2] ||=[]
        dep_graphs[ds2] << "\"#{that_obj.to_s}\" -> \"#{ds2}\"; \n"
      end
      if that_obj.to_s =~ /^TYPE: COMPOSITE (TABLE|VIEW)\\n/
        ds2 = that_obj.to_s.sub(/^TYPE: COMPOSITE (TABLE|VIEW)\\n/, '\1\n')
        dep_graphs[ds2] ||=[]
        dep_graphs[ds2] << "\"#{that_obj.to_s}\" -> \"#{ds2}\"; \n"
      end

    end
  end
end
print <<-EOT
digraph g {
  graph [rankdir = "RL",concentrate = true,ratio = auto, overlap=false];
  node [fontsize = "10"];
  edge [];
EOT
dep_graphs.each do |that, values|
 puts "subgraph \"#{that}\" {"
    values.uniq.each do |v|
	    a = v.split( / -> /)
	    puts v unless a[0] == "\"#{that}\""
    end
 puts "}"
end
print "}"

Simple helper script to quickly create images for dependencies subgraphs.

#!/bin/sh
# mk_dep_doc.sh conninfo splitprefix output_type
# e.g.:
# mk_dep_doc.sh "dbname=template1" template_split png
./pg_show_deps "$1" | ccomps -o "$2.dot" -x
find -name "$2*dot" -exec dot -T$3 -o{}.$3 {} \;
find -name "$2*dot" -exec echo "<a href={}>{}</a>   <a href={}.$3>Image</a><br/>" \; >index.html