table.rb in pgslice-0.4.6

- old
+ new

@@ -31,24 +31,28 @@
           JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
           JOIN pg_namespace n ON n.oid = s.relnamespace
         WHERE s.relkind = 'S'
           AND n.nspname = $1
           AND t.relname = $2
+        ORDER BY s.relname ASC
       SQL
       execute(query, [schema, name])
     end
 
     def foreign_keys
       execute("SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = #{regclass} AND contype ='f'").map { |r| r["pg_get_constraintdef"] }
     end
 
-    # http://stackoverflow.com/a/20537829
+    # https://stackoverflow.com/a/20537829
+    # TODO can simplify with array_position in Postgres 9.5+
     def primary_key
       query = <<-SQL
         SELECT
           pg_attribute.attname,
-          format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
+          format_type(pg_attribute.atttypid, pg_attribute.atttypmod),
+          pg_attribute.attnum,
+          pg_index.indkey
         FROM
           pg_index, pg_class, pg_attribute, pg_namespace
         WHERE
           nspname = $1 AND
           relname = $2 AND
@@ -56,11 +60,12 @@
           pg_class.relnamespace = pg_namespace.oid AND
           pg_attribute.attrelid = pg_class.oid AND
           pg_attribute.attnum = any(pg_index.indkey) AND
           indisprimary
       SQL
-      execute(query, [schema, name]).map { |r| r["attname"] }
+      rows = execute(query, [schema, name])
+      rows.sort_by { |r| r["indkey"].split(" ").index(r["attnum"]) }.map { |r| r["attname"] }
     end
 
     def index_defs
       execute("SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = #{regclass} AND indisprimary = 'f'").map { |r| r["pg_get_indexdef"] }
     end
@@ -115,10 +120,11 @@
           JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
           JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
         WHERE
           nmsp_parent.nspname = $1 AND
           parent.relname = $2
+        ORDER BY child.relname ASC
       SQL
       execute(query, [schema, name]).map { |r| Table.new(r["schema"], r["name"]) }
     end
 
     def fetch_comment
@@ -142,10 +148,10 @@
       unless period
         needs_comment = true
         function_def = execute("SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = $1", [trigger_name])[0]
         return [] unless function_def
         function_def = function_def["pg_get_functiondef"]
-        sql_format = SQL_FORMAT.find { |_, f| function_def.include?("'#{f}'") }
+        sql_format = Helpers::SQL_FORMAT.find { |_, f| function_def.include?("'#{f}'") }
         return [] unless sql_format
         period = sql_format[0]
         field = /to_char\(NEW\.(\w+),/.match(function_def)[1]
       end
 