1  
   2  pg_dump dbname > dbname.sql
   3  cat dbname.sql | psql dbname

   1  
   2  #!/bin/env ruby
   3  #
   4  # pg_show_deps: outputs dependencies graph for objects (tables, types, proc, views) in PostgreSQL database schema.
   5  # output is produced in dot format and can be processed using graphviz (http://www.graphviz.org/) toolset
   6  # to get a picture of dependencies.
   7  #
   8  # Sample usage:
   9  #    pg_show_deps 'dbname=template1 port=5432 user=looser' >template1.dot
  10  #    ccomps -otemplate_split.dot -x template1.dot
  11  #    dot -Tps -o template_2.ps template_split_2.dot
  12  #
  13  # Developed using PostgreSQL 8.1, 
  14  # requires ruby-postgres driver (http://rubyforge.org/projects/ruby-postgres)
  15  
  16  require 'postgres'
  17  $PG_CLASSES = {}
  18              
  19  class DBObject
  20    attr_accessor :row, :o_type, :nsp
  21    def initialize(conn, class_id, obj_id, sub_id)
  22      @o_type   = $PG_CLASSES[class_id]
  23      @row   = conn.query(sql_for(@o_type, obj_id, sub_id)).first
  24      @nsp = row['nsp']
  25      if @o_type == 'pg_proc'
  26        arg_types = row.last.split(" ")
  27        unless arg_types.empty?
  28          arg_type_names = arg_types.map {|oid| "format_type(#{oid}, -1)"}.join(", ")
  29          row[-1] = "("+conn.query("SELECT #{arg_type_names}").first.join(", ") +")"
  30        else 
  31  	     row[-1] = "()"
  32        end
  33      end
  34    end
  35    def sql_for(pg_class, obj_id, sub_id)
  36    base_sql = case pg_class 
  37      when 'pg_type' 
  38        "select       (case 
  39        	when typtype = 'b' then 'BASE '
  40  	when typtype = 'c' then 'COMPOSITE '
  41  	when typtype = 'd' then 'DOMAIN '
  42  	when typtype = 'p' then 'PSEUDO '
  43        end) || coalesce( 
  44        (select (CASE WHEN relkind = 'r' THEN 'TABLE'
  45              WHEN relkind = 'v' THEN 'VIEW'
  46              WHEN relkind = 'i' THEN 'INDEX'
  47              WHEN relkind = 'S' THEN 'SEQUENCE'
  48              WHEN relkind = 's' THEN 'SPECIAL'
  49              WHEN relkind = 't' THEN 'TOAST'
  50  	    WHEN relkind = 'c' THEN ' '
  51          END) from pg_class c where c.oid = typrelid), ' '), 
  52  (select nspname from pg_namespace n where n.oid = typnamespace) as nsp,       typname as obj_name,
  53        #{sub_id} from pg_type  "
  54      when 'pg_proc' 
  55        "select (select nspname from pg_namespace n where n.oid = pronamespace) as nsp, proname as obj_name, proargtypes from pg_proc "
  56      when 'pg_class'
  57        "select 
  58    (CASE WHEN relkind = 'r' THEN 'TABLE'
  59              WHEN relkind = 'v' THEN 'VIEW'
  60              WHEN relkind = 'i' THEN 'INDEX'
  61              WHEN relkind = 'S' THEN 'SEQUENCE'
  62              WHEN relkind = 's' THEN 'SPECIAL'
  63              WHEN relkind = 't' THEN 'TOAST'
  64          END) , (select nspname from pg_namespace n where n.oid = relnamespace) as nsp, 
  65         relname as obj_name,  
  66        (select attname from pg_attribute where attrelid = #{obj_id} and attnum = #{sub_id}) from pg_class"
  67      when 'pg_rewrite'
  68        "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"
  69      else nil
  70    end
  71    base_sql = base_sql ? base_sql << " WHERE oid = #{obj_id} ": nil
  72    
  73  end
  74   
  75    def to_s
  76      case @o_type
  77      	when 'pg_proc' : "FUNCTION\\n#{@row[0]}.#{@row[1]}#{@row[2]}"
  78  	when 'pg_type': "TYPE: #{@row[0]}\\n#{@row[1]}.#{@row[2]}"
  79  	when 'pg_class' : "#{@row[0]}\\n#{@row[1]}.#{@row[2]}" + (row[3] ? "\\nCOLUMN #{@row[3]}" : "")
  80  	when 'pg_rewrite' :  @row[1] == '_RETURN' ? "VIEW\\n#{@row[0]}.#{@row[2]}" : "RULE #{@row[1]} ON \\n#{@row[0]}.#{@row[2]}"
  81      end 
  82    end
  83  end
  84  
  85  # connection to database
  86  conn=PGconn.new(ARGV[0])
  87  
  88  conn.query("select distinct classid, relname from pg_class c join pg_depend d on (c.oid = d.classid)").each do |row|
  89    $PG_CLASSES[row['classid']] = row['relname']
  90  end
  91  conn.query("select distinct refclassid, relname from pg_class c join pg_depend d on (c.oid = d.refclassid)").each do |row|
  92    $PG_CLASSES[row['refclassid']] = row['relname']
  93  end
  94  ACCEPTED = ['pg_type', 'pg_proc', 'pg_class', 'pg_rewrite']
  95  dep_graphs = {}
  96  conn.query("SELECT * FROM pg_catalog.pg_depend   where   deptype<> 'i' and deptype <> 'p'").each do |row|
  97    if ACCEPTED.include?($PG_CLASSES[row['classid']]) && ACCEPTED.include?($PG_CLASSES[row['refclassid']])
  98      this_obj = DBObject.new(conn, row['classid'], row['objid'], row['objsubid'])
  99      that_obj = DBObject.new(conn, row['refclassid'], row['refobjid'], row['refobjsubid'])
 100      #dep_string = this_obj.nsp !~ /^pg_toast$/ ? "\"#{this_obj.to_s}\" -> \"#{that_obj.to_s}\"; \n" : nil
 101      dep_string = this_obj.nsp !~ /^(information_schema|pg_catalog|pg_toast)$/  ? "\"#{this_obj.to_s}\" -> \"#{that_obj.to_s}\"; \n" : nil
 102      
 103      if dep_string && this_obj.to_s !~ /^INDEX\\n/
 104        dep_graphs[that_obj.to_s] ||=[]
 105        dep_graphs[that_obj.to_s] << dep_string
 106        if that_obj.to_s =~ /^(VIEW|TABLE)\\n(.+?)\\nCOLUMN /m
 107          ds2 = that_obj.to_s.sub(/\\nCOLUMN.*/m, '')
 108          dep_graphs[ds2] ||=[]
 109          dep_graphs[ds2] << "\"#{that_obj.to_s}\" -> \"#{ds2}\"; \n"
 110        end
 111        if that_obj.to_s =~ /^TYPE: COMPOSITE (TABLE|VIEW)\\n/
 112          ds2 = that_obj.to_s.sub(/^TYPE: COMPOSITE (TABLE|VIEW)\\n/, '\1\n')
 113          dep_graphs[ds2] ||=[]
 114          dep_graphs[ds2] << "\"#{that_obj.to_s}\" -> \"#{ds2}\"; \n"
 115        end
 116  
 117      end
 118    end
 119  end
 120  print <<-EOT
 121  digraph g {
 122    graph [rankdir = "RL",concentrate = true,ratio = auto, overlap=false];
 123    node [fontsize = "10"];
 124    edge [];
 125  EOT
 126  dep_graphs.each do |that, values|
 127   puts "subgraph \"#{that}\" {"
 128      values.uniq.each do |v|
 129  	    a = v.split( / -> /)
 130  	    puts v unless a[0] == "\"#{that}\""
 131      end
 132   puts "}"
 133  end
 134  print "}"

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

   1  
   2  site:api.rubyonrails.com submit_tag

   1  
   2  http://www.google.com/search?hl=en&q=site%3Aapi.rubyonrails.com+%s

   1  
   2  http://www.google.com/search?hl=en&q=site%3Ahttp%3A%2F%2Fwrath.rubyonrails.org%2Fpipermail%2Frails%2F+%s

   1  
   2  http://www.google.com/search?hl=en&lr=&q=site%3Ahttp%3A%2F%2Fwww.postgresql.org%2Fdocs%2F8.1%2Finteractive%2F+%s

   1  
   2  # Just for fun and horror: make database functions a part of your Rails model! :)
   3  #
   4  # Class to access to PostgreSQL functions. Returned value depends on params and query result, see below.
   5  #
   6  # Currently supported options are:
   7  #     :order => '1 desc' # to add order clause
   8  #     :use_from => true  # to add "* from" for non-model function queries, which return records
   9  #     :all => true       # to return not first but all found models
  10  #     :cast => string    # to cast result (useful for functions, returning +setof record+)
  11  #
  12  # Call-patterns:
  13  #
  14  # A) Model loading from functions that return setof system known rowtype
  15  #     PgProc.function(ModelClass[, options])
  16  #         PgProc.get_descendants(ContentNode, 123)
  17  #
  18  #     PgProc.function(ModelClass, value[, options ])
  19  #         PgProc.get_children(ContentNode, 123, :order => 'position', :all => true)
  20  #
  21  # returns either:
  22  # * empty array if nothing found
  23  # * first found model object, if found only one and +:all+ option is not set
  24  # * array of model objects
  25  #
  26  # B) Values from functions
  27  #
  28  # PgProc.function(:type_symbol, value, [type_symbol2, value2, ...[, options]]) - for explicit parameter typecast
  29  #     PgProc.array_append(:"int[]", '{1,2,3,4}', :int, 5) # => {1,2,3,4,5}
  30  #
  31  # PgProc.function(*args [, options])
  32  #     PgProc.generate_series(1,10,2, :order => '1 desc') # => [9,5,7,3,2,1]
  33  #
  34  # PgProc.function() - for functions w/o params
  35  #     PgProc.now()
  36  #
  37  # returns either:
  38  # * empty string for +void+ functions
  39  # * single value, if resultset has 1x1 dimension
  40  # * array of values if resultset has Nx1 dimension (N>1)
  41  # * array of rows otherwise
  42  #
  43  # Throws PGError, if function doesn't exist or wrong params supplied
  44  
  45  class PgProc < ActiveRecord::Base
  46      set_table_name 'pg_catalog.pg_proc'
  47      set_primary_key 'oid'
  48      def readonly?
  49        true
  50      end
  51  
  52  private
  53      def self.method_missing(meth_sym, *args)
  54        func_name = meth_sym.id2name
  55        super unless find(:first, :conditions => ['proname = ?', func_name])
  56        if ! args.empty? && args.last.is_a?(Hash)
  57          options = args.pop
  58          order_str = " ORDER BY #{options[:order]}" if options[:order]
  59        else
  60          options = {}
  61          order_str = nil
  62        end
  63        from_str = " * FROM " if options[:use_from]
  64        if args.empty?
  65          temp = connection.query("select #{from_str} #{func_name}() #{options[:cast]} #{order_str}")
  66        elsif args.first.is_a?(Class)
  67          model_klass = args.shift
  68          if args.length == 0
  69            temp = model_klass.find_by_sql("select * from  #{func_name}()  #{options[:cast]} #{order_str}")
  70          else
  71            temp = model_klass.find_by_sql("select * from  #{func_name}(#{quote_bound_value(args)})  #{options[:cast]} #{order_str}")
  72          end
  73          return temp if options[:all]
  74          return temp.length == 1 ? temp.first : temp
  75        else
  76            if args.length % 2 == 0 && args.first.is_a?(Symbol)
  77              temp = connection.query("select #{from_str} #{func_name}(#{quote_bound_value_types(args)}) #{options[:cast]} #{order_str}")
  78            else
  79              temp = connection.query("select #{from_str} #{func_name}(#{quote_bound_value(args)})  #{options[:cast]} #{order_str}")
  80            end
  81        end
  82        return temp.first.first if temp.length == 1 && temp.first.length == 1
  83        return temp.flatten if temp.length > 1 && temp.first.length == 1
  84        return temp
  85      end
  86      def self.quote_bound_value_types(value)
  87        i = true
  88        value.partition {|v| i = !i }.transpose.map{|v| "#{connection.quote(v[0])}::#{v[1]}"}.join(',')
  89      end
  90  end

   1  
   2  #!/bin/env ruby
   3  # pg_diff - compare two PostgreSQL database schemas
   4  #
   5  # URL: http://snippets.dzone.com/posts/show/949
   6  #
   7  # This is a simple approach to track database schema changes in PostgreSQL.
   8  # In some way it is similar to diff program, finding out structure changes
   9  # and results in  SQL script to upgrade to new schema.
  10  # 
  11  # Differences are tracked on schemas, domains, sequences, views, tables, indices, constraints, rules, functions, triggers.
  12  # Two objects with the same name are considered equal if they have the same definitions. 
  13  #
  14  # Missing features: tracking of ownership,  user rights, object dependencies, table inheritance, type casts, aggregates, operators.
  15  # 
  16  # Usage:
  17  #    ./pg_diff dbname=db_v03_dev dbname=db_v04_dev
  18  # 
  19  # Developed using PostgreSQL v8.0.3, v8.1 with ruby-postgres libpq binding (20051127 snapshot).
  20  # 
  21  # This software is released under MIT License
  22  # 
  23  # Copyright (c) 2005 Dmitry Severin <dmitry.severin (at) gmail.com>
  24  #
  25  # Permission is hereby granted, free of charge, to any person obtaining a copy
  26  # of this software and associated documentation files (the "Software"), to deal
  27  # in the Software without restriction, including without limitation the rights
  28  # to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  29  # copies of the Software, and to permit persons to whom the Software is
  30  # furnished to do so, subject to the following conditions:
  31  # 
  32  # The above copyright notice and this permission notice shall be included in
  33  # all copies or substantial portions of the Software.
  34  # 
  35  # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  36  # IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  37  # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  38  # AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  39  # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  40  # OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
  41  # THE SOFTWARE.
  42  #
  43  
  44  require 'postgres'
  45  module PostgreSqlSchema
  46  
  47  class Attribute
  48    attr_accessor :name, :type_def, :notnull, :default
  49    def initialize(name, typedef, notnull, default)
  50      @name = name
  51      @type_def = typedef
  52      @notnull = notnull
  53      @default = default
  54    end
  55    def definition
  56      out = ['    ', @name,  @type_def]
  57      out << 'NOT NULL' if @notnull
  58      out << 'DEFAULT ' + @default if @default
  59      out.join(" ")
  60    end
  61    def == (other)
  62      definition == other.definition
  63    end
  64  end
  65  
  66  class Table
  67    attr_accessor :table_name, :schema, :attributes, :constraints, :indexes
  68    
  69    def initialize(conn, schema, table_name)
  70      @schema = schema
  71      @table_name = table_name
  72      @attributes = {}
  73      @constraints = {}
  74      @indexes = {}
  75      @atlist = []
  76      
  77      att_query = <<-EOT
  78        select attname, format_type(atttypid, atttypmod) as a_type, attnotnull,  pg_get_expr(adbin, attrelid) as a_default 
  79        from pg_attribute left join pg_attrdef  on (adrelid = attrelid and adnum = attnum) 
  80        where attrelid = '#{schema}.#{table_name}'::regclass and not attisdropped and attnum > 0 
  81        order by attnum
  82      EOT
  83      conn.query(att_query).each do |row|
  84        attname = row[0]
  85        @attributes[attname] = Attribute.new(attname, row[1], row[2], row[3])
  86        @atlist << attname
  87      end
  88    
  89      ind_query = <<-EOT
  90        select indexrelid::regclass as indname, pg_get_indexdef(indexrelid) as def 
  91        from pg_index where indrelid = '#{schema}.#{table_name}'::regclass and not indisprimary
  92      EOT
  93      conn.query(ind_query).each do |row|
  94        @indexes[row[0]] = row[1]
  95      end
  96  
  97      cons_query = <<-EOT
  98        select conname, pg_get_constraintdef(oid) from pg_constraint where conrelid = '#{schema}.#{table_name}'::regclass
  99      EOT
 100      conn.query(cons_query).each do |row|
 101        @constraints[row[0]] = row[1]
 102      end
 103      @constraints.keys.each do |cname|
 104        @indexes.delete("#{schema}.#{cname}") if has_index?(cname)
 105      end
 106    end
 107    
 108    def has_attribute?(name)
 109      @attributes.has_key?(name)
 110    end
 111  
 112    def has_index?(name)
 113      @indexes.has_key?(name) || @indexes.has_key?("#{schema}.#{name}")
 114    end
 115    
 116    def has_constraint?(name)
 117      @constraints.has_key?(name)
 118    end
 119    
 120    def table_creation
 121      out = ["CREATE TABLE #{name} ("]
 122      stmt = []
 123      @atlist.each do |attname|
 124        stmt << @attributes[attname].definition
 125      end
 126      out << stmt.join(",\n")
 127      out << ");"
 128      out.join("\n")
 129    end
 130  
 131    def name
 132      "#{schema}.#{table_name}"
 133    end
 134    
 135    def constr_creation
 136      out = []
 137      @constraints.each do |n, c|
 138        out << "ALTER TABLE #{name} ADD CONSTRAINT #{n} #{c};" 
 139      end
 140      out.join("\n")
 141    end
 142    
 143    def index_creation
 144      out = []
 145      @indexes.values.each do |c|
 146        out << (c+";")
 147      end
 148      out.join("\n")
 149    end
 150  end
 151  
 152  class Sequence
 153  
 154    def initialize(conn, sch, relname)
 155       @name = "#{sch}.#{relname}"
 156    end
 157    
 158    def definition
 159      "CREATE SEQUENCE #{@name} ;"
 160    end
 161  end
 162  
 163  class View
 164    attr_reader :def, :name 
 165    
 166    def initialize(conn, sch, relname)
 167      @name = "#{sch}.#{relname}"
 168      view_qery = <<-EOT
 169        SELECT pg_catalog.pg_get_viewdef('#{@name}'::regclass, true)
 170      EOT
 171      @def = conn.query(view_qery)[0][0]
 172    end
 173    
 174    def definition
 175      "CREATE VIEW #{@name} AS #{@def}"
 176    end
 177  end
 178  
 179  class Database
 180    attr_accessor :tables, :views, :sequences, :schemas, :domains, :rules, :functions, :triggers
 181    def initialize(conn)
 182       cls_query = <<-EOT
 183        SELECT n.nspname, c.relname, c.relkind
 184        FROM pg_catalog.pg_class c
 185        LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
 186        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 187        WHERE c.relkind IN ('r','S','v')
 188        AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
 189        ORDER BY 1,2;
 190      EOT
 191      @views = {}
 192      @tables = {}
 193      @sequences = {}
 194      @schemas = {}
 195      @domains = {}
 196      @functions = {}
 197      @rules = {}
 198      @triggers = {}
 199      
 200      conn.query(cls_query).each do |row|
 201        schema, relname, relkind = row
 202        case relkind
 203          when 'r' then @tables["#{schema}.#{relname}"] = Table.new(conn, schema, relname)
 204          when 'v' then @views ["#{schema}.#{relname}"] = View.new(conn, schema, relname)
 205          when 'S' then @sequences["#{schema}.#{relname}"] = Sequence.new(conn, schema, relname)
 206        end
 207      end
 208      
 209    domain_qry = <<-EOT
 210      SELECT n.nspname, t.typname,  pg_catalog.format_type(t.typbasetype, t.typtypmod) || ' ' ||
 211         CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault
 212              WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'
 213              WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault
 214              ELSE ''
 215         END 
 216      FROM pg_catalog.pg_type t
 217         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
 218      WHERE t.typtype = 'd'
 219      ORDER BY 1, 2
 220    EOT
 221      conn.query(domain_qry).each do |row|
 222        @domains["#{row[0]}.#{row[1]}"] = row[2]
 223      end
 224  
 225      schema_qry = <<-EOT
 226        select nspname from pg_namespace
 227      EOT
 228      conn.query(schema_qry).each do |row|
 229        @schemas[row[0]]=row[0]
 230      end
 231  
 232      func_query = <<-EOT
 233       SELECT proname AS function_name
 234       , nspname AS namespace
 235       , lanname AS language_name
 236       , pg_catalog.obj_description(pg_proc.oid, 'pg_proc') AS comment
 237       , proargtypes AS function_args
 238       , proargnames AS function_arg_names
 239       , prosrc AS source_code
 240       , proretset AS returns_set
 241       , prorettype AS return_type,
 242       provolatile, proisstrict, prosecdef
 243       FROM pg_catalog.pg_proc
 244       JOIN pg_catalog.pg_language ON (pg_language.oid = prolang)
 245       JOIN pg_catalog.pg_namespace ON (pronamespace = pg_namespace.oid)
 246       JOIN pg_catalog.pg_type ON (prorettype = pg_type.oid)
 247       WHERE pg_namespace.nspname !~ 'pg_catalog|information_schema'
 248       AND proname != 'plpgsql_call_handler'
 249       AND proname != 'plpgsql_validator'
 250      EOT
 251      conn.exec(func_query).result.each do |tuple|
 252        func = Function.new(conn, tuple)
 253        @functions[func.signature] = func 
 254      end
 255  
 256      rule_query = <<-EOT
 257      select  schemaname || '.' ||  tablename || '.' || rulename as rule_name, 
 258              schemaname || '.' ||  tablename as tab_name,
 259        rulename, definition
 260      from pg_rules
 261      where schemaname !~ 'pg_catalog|information_schema'
 262      EOT
 263      conn.exec(rule_query).result.each do |tuple|
 264        @rules[tuple['rule_name']] = Rule.new(tuple['tab_name'], tuple['rulename'], tuple['definition'])
 265      end
 266  
 267      trigger_query =  <<-EOT
 268      select nspname || '.' || relname as tgtable, tgname, pg_get_triggerdef(t.oid) as tg_def
 269      from pg_trigger t join pg_class c ON (tgrelid = c.oid ) JOIN pg_namespace n ON (c.relnamespace = n.oid)
 270      where not tgisconstraint
 271      and nspname !~ 'pg_catalog|information_schema'
 272      EOT
 273      conn.exec(trigger_query).result.each do |tuple|
 274        @triggers[tuple['tgtable'] + "." + tuple['tgname']] = Trigger.new(tuple['tgtable'], tuple['tgname'], tuple['tg_def'])
 275      end
 276    end
 277  end
 278  
 279  class Rule
 280    attr_reader :table_name, :name, :definition
 281    def initialize(table_name, name, df)
 282      @table_name = table_name
 283      @name = name
 284      @definition = df
 285    end
 286    def == (other)
 287      other.definition == definition
 288    end
 289  end
 290  
 291  class Trigger
 292    attr_reader :table_name, :name, :definition
 293    def initialize(table_name, name, df)
 294      @table_name = table_name
 295      @name = name
 296      @definition = df + ";"
 297    end
 298    def == (other)
 299      other.definition == definition
 300    end
 301  end
 302  
 303  class Function
 304    def initialize(conn, tuple)
 305      @name = tuple['namespace'] + "." + tuple['function_name']
 306      @language = tuple['language_name']
 307      @src = tuple['source_code']
 308      @returns_set = tuple['returns_set']
 309      @return_type = format_type(conn, tuple['return_type'])
 310      @tipes = tuple['function_args'].split(" ")
 311      if tuple['function_arg_names'] && tuple['function_arg_names'] =~ /^\{(.*)\}$/
 312        @arnames = $1.split(',')
 313      elsif tuple['function_arg_names'].is_a? Array # my version of ruby-postgres
 314        @arnames = tuple['function_arg_names']
 315      else
 316        @arnames = [""] * @tipes.length
 317      end
 318      alist = []
 319      @tipes.each_with_index do |typ,idx|
 320        alist << (@arnames[idx] +" " + format_type(conn, typ))
 321      end
 322      @arglist = alist.join(" , ")
 323      @strict = tuple['proisstrict'] ? ' STRICT' : ''
 324      @secdef = tuple['prosecdef'] ? ' SECURITY DEFINER' : ''
 325      @volatile = case tuple['provolatile']
 326        when 'i' then ' IMMUTABLE'
 327        when 's' then ' STABLE'
 328        else ''
 329      end
 330    end
 331    def signature
 332      "#{@name}(#{@arglist})"
 333    end
 334    def definition
 335      <<-EOT
 336  CREATE OR REPLACE FUNCTION #{@name} (#{@arglist}) RETURNS #{@returns_set ? 'SETOF' : ''} #{@return_type} AS $_$#{@src}$_$ LANGUAGE '#{@language}' #{@volatile}#{@strict}#{@secdef};
 337  EOT
 338    end
 339    def == (other)
 340      definition == other.definition
 341    end
 342    def format_type(conn, oid)
 343      t_query = <<-EOT
 344      SELECT pg_catalog.format_type(pg_type.oid, typtypmod) AS type_name
 345       FROM pg_catalog.pg_type
 346       JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = typnamespace)
 347       WHERE pg_type.oid = 
 348      EOT
 349      return conn.query(t_query + oid.to_s)[0][0]
 350    end
 351  end
 352  class Diff
 353   
 354    def initialize(old_db_spec, new_db_spec)
 355      @old_conn = PGconn.new(old_db_spec)
 356      @new_conn = PGconn.new(new_db_spec)
 357      @sections = [
 358        :triggers_drop,
 359        :rules_drop,
 360        :functions_drop,
 361        :indices_drop ,
 362        :constraints_drop,
 363        :views_drop,
 364        :sequences_drop ,
 365        :tables_drop ,
 366        :domains_drop ,
 367        :schemas_drop , 
 368        :schemas_create,
 369        :domains_create,
 370        :sequences_create,