PostgreSQL: generate DDL to alter views with dependencies (See related posts)

#!/usr/bin/ruby -W0
#
# This script tries to determine all dependencies on given table/view or their columns, 
# types (base/domain/composite), functions, rules.
#
# Usage:
#       ruby show_obj_deps conn_string object_id alter_stmt 
# e.g:
#       ruby show_obj_deps dbname=db1 "VIEW public.base_stats" "DROP VIEW public.base_stats; CREATE VIEW public.base_stats AS SELECT ..."
#
# Result will be series of DDL DROP statements for dependent objects, then alter_stmt, and
# then series of DDL CREATE statements for dropped objects.
#
# Format of object_id:
#   TABLE schema_name.table_name
#   VIEW schema_name.view_name
#   TABLE schema_name.table_name COLUMN column_name
#   VIEW schema_name.view_name COLUMN column_name
#   FUNCTION schema_name.func_name(type_1, type2, ...)
#   RULE rule_name ON schema_name.obj_name
#
# This script is somewhat rewritten version of http://snippets.dzone.com/posts/show/2105
#
# Developed using PostgreSQL v8.0.3, v8.1 with ruby-postgres libpq binding
# 

require 'postgres'
require 'tsort'
require 'pp'
PGconn.translate_results = true

$PG_CLASSES = {}
class PgDependencyGraph            
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
        puts "IGNORE: #{pg_class}, #{obj_id}, #{sub_id}"
        nil
  end
  base_sql = base_sql ? base_sql << " WHERE oid = #{obj_id} ": nil
  
end
 
  def to_s
    case @o_type
    	when 'pg_proc' : "FUNCTION #{@row[0]}.#{@row[1]}#{@row[2]}"
      when 'pg_type': "TYPE: #{@row[0]} #{@row[1]}.#{@row[2]}"
      when 'pg_class' : "#{@row[0]} #{@row[1]}.#{@row[2]}" + (row[3] ? " COLUMN #{@row[3]}" : "")
      when 'pg_rewrite' :  @row[1] == '_RETURN' ? "VIEW #{@row[0]}.#{@row[2]}" : "RULE #{@row[1]} ON #{@row[0]}.#{@row[2]}"
    end 
  end
end
  attr_accessor :depend_graph
    ACCEPTED = ['pg_type', 'pg_proc', 'pg_class', 'pg_rewrite']
  def initialize(conn)
    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
    dep_graphs = {}
    conn.query("SELECT * FROM pg_catalog.pg_depend   where  true or ( 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 : nil
    
      if dep_string && this_obj.to_s !~ /^INDEX /
        dep_graphs[that_obj.to_s] ||=[]
        dep_graphs[that_obj.to_s] << dep_string
        if that_obj.to_s =~ /^(VIEW|TABLE) (.+?) COLUMN /m
          ds2 = that_obj.to_s.sub(/ COLUMN.*/m, '')
          dep_graphs[ds2] ||=[]
          dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2
        elsif that_obj.to_s =~ /^TYPE: COMPOSITE (TABLE|VIEW) /
          ds2 = that_obj.to_s.sub(/^TYPE: COMPOSITE (TABLE|VIEW) /, '\1 ')
          dep_graphs[ds2] ||=[]
          dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2
        end
      end
    end
  end
  dep_graphs.each do |that, values|
    values.uniq!
    values.reject! {|item| item == that}
  end
  @depend_graph = dep_graphs
 end
 def list_dependencies(obj)
    dep_list = []
    if @depend_graph[obj]
      @depend_graph[obj].each do |v|
        k = list_dependencies(v)
        k.empty? ? dep_list << v : dep_list << [v, k]
      end
    end
    dep_list
 end
end

class DG
 include TSort
 def initialize(dep_graph, node_list)
   @nodes = node_list
   @dg = dep_graph
 end
 def tsort_each_node(&block)
   @nodes.each {|x| yield x}
 end
 def tsort_each_child(node, &block)
   (@dg[node]||[]).each(&block)
 end
end    

class Function
  attr_reader :typed_head
  def initialize(conn, tuple)
    @name = tuple['namespace'] + "." + tuple['function_name']
    @language = tuple['language_name']
    @src = tuple['source_code']
    @returns_set = tuple['returns_set']
    @return_type = format_type(conn, tuple['return_type'])
    @tipes = tuple['function_args'].split(" ")
    if tuple['function_arg_names'] && tuple['function_arg_names'] =~ /^\{(.*)\}$/
      @arnames = $1.split(',')
    elsif tuple['function_arg_names'].is_a? Array
      @arnames = tuple['function_arg_names']
    else
      @arnames = [""] * @tipes.length
    end
    alist = []
    atypelist = [] 
    @tipes.each_with_index do |typ,idx|
      ft = format_type(conn, typ)
      alist << (@arnames[idx] +" " + ft)
      atypelist << ft
    end
    @arglist = alist.join(" , ")
    @strict = tuple['proisstrict'] ? ' STRICT' : ''
    @secdef = tuple['prosecdef'] ? ' SECURITY DEFINER' : ''
    @volatile = case tuple['provolatile']
      when 'i' then ' IMMUTABLE'
      when 's' then ' STABLE'
      else ''
    end
    @typed_head = @name+"("+atypelist.join(", ")+")"
  end
  def signature
    "#{@name}(#{@arglist})"
  end
  def definition
    <<-EOT
CREATE OR REPLACE FUNCTION #{@name} (#{@arglist}) RETURNS #{@returns_set ?  'SETOF' : ''} #{@return_type} AS $_$#{@src}$_$ LANGUAGE '#{@language}' #{@volatile}#{@strict}#{@secdef};
EOT
  end
  def == (other)
    definition == other.definition
  end
  def format_type(conn, oid)
    t_query = <<-EOT
    SELECT pg_catalog.format_type(pg_type.oid, typtypmod) AS type_name
     FROM pg_catalog.pg_type
     JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = typnamespace)
     WHERE pg_type.oid = 
    EOT
    return conn.query(t_query + oid.to_s)[0][0]
  end
  def self.find(conn, schema, name, args)
    func_query = <<-EOT
     SELECT proname AS function_name
     , nspname AS namespace
     , lanname AS language_name
     , pg_catalog.obj_description(pg_proc.oid, 'pg_proc') AS comment
     , proargtypes AS function_args
     , proargnames AS function_arg_names
     , prosrc AS source_code
     , proretset AS returns_set
     , prorettype AS return_type,
     provolatile, proisstrict, prosecdef
     FROM pg_catalog.pg_proc
     JOIN pg_catalog.pg_language ON (pg_language.oid = prolang)
     JOIN pg_catalog.pg_namespace ON (pronamespace = pg_namespace.oid)
     JOIN pg_catalog.pg_type ON (prorettype = pg_type.oid)
     WHERE pg_namespace.nspname !~ 'pg_catalog|information_schema|pg_temp_'
     AND nspname = $1
     AND proname = $2
     AND oidvectortypes(proargtypes) = $3
    EOT

    Function.new(conn, conn.query(func_query, schema, name, args).first)
  end
end

conn = PGconn.new(ARGV[0])
graph = PgDependencyGraph.new(conn)
#pp graph.list_dependencies(ARGV[1])
dep_list = []
ARGV[1].split(/\|/).each do |obj_id|
  dep_list += graph.list_dependencies(obj_id).flatten.uniq
end
dep_list.uniq!

top_sorted = DG.new(graph.depend_graph, dep_list).tsort
top_sorted.each do |line|
  case line
        when /^TYPE:/, /^VIEW (\S+) COLUMN/; 
        when /^VIEW (.+)/ then puts "DROP VIEW #$1;"
        when /^FUNCTION (.+)/ then puts "DROP FUNCTION #$1;"
        when /^RULE / then puts "DROP "+line+";"
        else 
        puts "-- SKIP #{line}"
  end
end

puts "", "--- ", "--- ALTER: ", "---"
puts ARGV[2]
puts "---", "---", ""
def view_def(conn, name)
  conn.select_value("SELECT pg_catalog.pg_get_viewdef('#{name}'::regclass, true)")
end
def rule_def(conn, tablename, rule_name)
 conn.select_value("select definition from pg_rules  where schemaname || '.' ||  tablename = $1  and rulename = $2", tablename, rule_name)
end

def func_def(conn, *args)
 Function.find(conn, *args).definition
end


top_sorted.reverse.each do |line|
  case line
        when /^TYPE:/, /^VIEW (\S+) COLUMN/ then puts "-- SKIP #{line}"
        when /^VIEW (.+)/ then puts "CREATE VIEW #$1 AS "+view_def(conn, $1)
        when /^FUNCTION (\w+)\.(\w+)\((.*)\)/ then puts func_def(conn, $1, $2, $3)
        when /^RULE (.+) ON (.+)/ then puts "CREATE "+rule_def(conn, $2, $1)
        else
          puts "-- SKIP #{line}"
  end
  puts 
end