pg_conn.rb in pg_conn-0.19.0

- old
+ new

@@ -245,11 +245,11 @@
     #
     # The value can be of any type but is converted to a string using #to_s
     # before quoting. This works by default for the regular types Integer,
     # true/false, Time/Date/DateTime, and arrays. Other types may require
     # special handling
-    def quote_literal(value) 
+    def quote_value(value) 
       case value
         when String; @pg_connection.escape_literal(value)
         when Integer, Float; value.to_s
         when true, false; value.to_s
         when nil; 'null'
@@ -259,16 +259,27 @@
       else
         @pg_connection.escape_literal(value.to_s)
       end
     end
 
-    # Quote array as a parenthesis-enclosed list of identifiers
-    def quote_identifier_list(identifiers) = "(#{identifiers.map { quote_identifier(_1) }.join(', ')})"
 
-    # Quote array as a parenthesis-enclosed list of literals
-    def quote_literal_list(values) = "(#{values.map { quote_literal(_1) }.join(', ')})"
+    # Quote array as a comma-separated sequence of identifiers
+    def quote_identifier_seq(identifiers) = identifiers.map { quote_identifier(_1) }.join(', ')
 
+    # Quote array as a parenthesis-enclosed sequence of identifiers
+    def quote_identifier_list(identifiers) = "(#{quote_identifier_seq(identifiers)})"
+
+    # Quote array as a comma-separated sequence of values
+    def quote_value_seq(values) = values.map { quote_literal(_1) }.join(', ')
+
+    # Quote array as a parenthesis-enclosed list of value
+    def quote_value_list(values) = "(#{quote_value_seq(values)})"
+
+    # Old aliases. TODO Remove
+    def quote_literal(value) = quote_value(value)
+    def quote_literal_list(values) = quote_value_list(values)
+
     # :call-seq:
     #   exist?(query)
     #   exist?(table, id)
     #   eists?(table, where_clause)
     #
@@ -314,12 +325,12 @@
     end
 
     # Return a single value. It is an error if the query doesn't return a
     # single record with a single column. If :transaction is true, the query
     # will be executed in a transaction and also be committed if :commit is
-    # true (this is the default). It can be used to execute 'insert' statements
-    # with a 'returning' clause
+    # true (this is the default). It can also be used to execute 'insert'
+    # statements with a 'returning' clause
     def value(query) #, transaction: false, commit: true)
       r = pg_exec(query)
       check_1c(r)
       check_1r(r)
       r.values[0][0]
@@ -494,11 +505,11 @@
     end
 
     # Returns a hash from the first field to a tuple of the remaining fields.
     # If there is only one remaining field then that value is used instead of a
     # tuple of that value. The optional +key+ argument sets the mapping field
-    def map(query, key = nil)
+    def map(query, key = nil) # TODO Swap arguments
       r = pg_exec(query)
       begin
         key = (key || r.fname(0)).to_s
         key_index = r.fnumber(key.to_s)
         one = (r.nfields == 2)
@@ -512,10 +523,27 @@
         h[key_value] = (one ? row.first : row)
       }
       h
     end
 
+    def multimap(query, key = nil)
+      r = pg_exec(query)
+      begin
+        key = (key || r.fname(0)).to_s
+        key_index = r.fnumber(key.to_s)
+        one = (r.nfields == 2)
+      rescue ArgumentError
+        raise Error, "Can't find column #{key}"
+      end
+      h = {}
+      r.each_row { |row|
+        key_value = row.delete_at(key_index)
+        (h[key_value] ||= []) << (one ? row.first : row)
+      }
+      h
+    end
+
     # Return the value of calling the given function (which can be a String or
     # a Symbol and can contain the schema of the function). It dynamically
     # detects the structure of the result and return a value or an array of
     # values if the result contained only one column (like #value or #values),
     # a tuple if the record has multiple columns (like #tuple), and an array of
@@ -554,19 +582,62 @@
           r&.values
         end
       end
     end
 
-    # Insert record(s) in a table. Returns the id of the inserted record(s)
-    def insert(schema = nil, table, array_or_hash)
-      return [] if array_or_hash.empty?
-      table = [schema, table].compact.join(".")
-      is_array = array_or_hash.is_a?(Array)
-      array = is_array ? array_or_hash : [array_or_hash]
-      identifiers = quote_identifier_list(array.first.keys)
-      literals = array.map { |hash| quote_literal_list(hash.values) }.join(", ")
-      method = (is_array ? :values : :value)
-      self.send method, %(insert into #{table} #{identifiers} values #{literals} returning id)
+    # :call-seq*
+    #   insert(table, record|records)
+    #   insert(table, fields, record|records|tuples)
+    #   insert(schema, table, record|records)
+    #   insert(schema, table, fields, record|records|tuples)
+    #
+    # Insert record(s) in table and return id(s)
+    #
+    # There is no variant that takes a single tuple because it would then be
+    # impossible to have array or hash field values
+    def insert(*args)
+      # Add schema (=nil) if absent
+      args.unshift nil if args.size == 2 || (args.size == 3 && args[1].is_a?(Array))
+
+      # Add fields (=nil) if absent
+      args.insert(-2, nil) if !args[-2].is_a?(Array)
+
+      # Check number of arguments
+      args.size == 4 or raise ArgumentError, "Illegal number of arguments"
+
+      # Extract parameters
+      schema, table, fields, data = args
+
+      # Normalize table
+      table = schema ? "#{schema}.#{table}" : table
+
+      # Find method and normalize data
+      if data.is_a?(Array) 
+        if data.empty?
+          return []
+        elsif data.first.is_a?(Array)
+          method = :values
+          fields or raise ArgumentError
+          tuples = data
+        elsif data.first.is_a?(Hash)
+          method = :values
+          fields ||= data.first.keys
+          tuples = data.map { |record| fields.map { |field| record[field] } }
+        else
+          raise ArgumentError
+        end
+      elsif data.is_a?(Hash)
+        method = :value
+        fields ||= data.keys
+        tuples = [fields.map { |field| data[field] }]
+      else
+        raise ArgumentError
+      end
+
+      # Build and execute SQL statement
+      columns = quote_identifier_list(fields)
+      values = tuples.map { |tuple| quote_literal_list(tuple) }.join(', ')
+      self.send method, %(insert into #{table} #{columns} values #{values} returning id)
     end
 
     # Update record(s)
     def update(schema = nil, table, expr, hash)
       table = [schema, table].compact.join(".")