module Sequel::Postgres::AutoParameterizeInArray

Enable automatically parameterizing queries.

Public Instance Methods

complex_expression_sql_append(sql, op, args) click to toggle source

Transform column IN (…) expressions into column = ANY($) and column NOT IN (…) expressions into column != ALL($) using an array bound variable for the ANY/ALL argument, if all values inside the predicate are of the same type and the type is handled by the extension. This is the same optimization PostgreSQL performs internally, but this reduces the number of bound variables.

Calls superclass method
   # File lib/sequel/extensions/pg_auto_parameterize_in_array.rb
52 def complex_expression_sql_append(sql, op, args)
53   case op
54   when :IN, :"NOT IN"
55     l, r = args
56     if auto_param?(sql) && (type = _bound_variable_type_for_array(r))
57       if op == :IN 
58         op = :"="
59         func = :ANY
60       else
61         op = :!=
62         func = :ALL
63       end
64       args = [l, Sequel.function(func, Sequel.pg_array(r, type))]
65     end
66   end
67 
68   super
69 end

Private Instance Methods

_bound_variable_type_for_array(r) click to toggle source

The bound variable type string to use for the bound variable array. Returns nil if a bound variable should not be used for the array.

    # File lib/sequel/extensions/pg_auto_parameterize_in_array.rb
 75 def _bound_variable_type_for_array(r)
 76   return unless Array === r && r.size >= (db.typecast_value(:integer, db.opts[:pg_auto_parameterize_min_array_size]) || 2)
 77   classes = r.map(&:class)
 78   classes.uniq!
 79   classes.delete(NilClass)
 80   return unless classes.size == 1
 81 
 82   klass = classes[0]
 83   if klass == Integer
 84     # This branch is not taken on Ruby <2.4, because of the Fixnum/Bignum split.
 85     # However, that causes no problems as pg_auto_parameterize handles integer
 86     # arrays natively (though the SQL used is different)
 87     "int8"
 88   elsif klass == String
 89     "text" if db.typecast_value(:boolean, db.opts[:treat_string_list_as_text_array])
 90   elsif klass == BigDecimal
 91     "numeric"
 92   elsif klass == Date
 93     "date"
 94   elsif klass == Time
 95     @db.cast_type_literal(Time)
 96   elsif klass == Float
 97     # PostgreSQL treats literal floats as numeric, not double precision
 98     # But older versions of PostgreSQL don't handle Infinity/NaN in numeric
 99     r.all?{|v| v.nil? || v.finite?} ? "numeric" : "double precision"
100   elsif klass == Sequel::SQLTime
101     "time"
102   elsif klass == DateTime
103     @db.cast_type_literal(DateTime)
104   elsif klass == Sequel::SQL::Blob
105     "bytea"
106   end
107 end