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
 99 def complex_expression_sql_append(sql, op, args)
100   case op
101   when :IN, :"NOT IN"
102     l, r = args
103     if auto_param?(sql) && (type = _bound_variable_type_for_array(r))
104       if op == :IN 
105         op = :"="
106         func = :ANY
107       else
108         op = :!=
109         func = :ALL
110       end
111       args = [l, Sequel.function(func, _convert_array_to_pg_array_with_type(r, type))]
112     end
113   end
114 
115   super
116 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
122 def _bound_variable_type_for_array(r)
123   return unless Array === r && r.size >= pg_auto_parameterize_min_array_size
124   classes = r.map(&:class)
125   classes.uniq!
126   classes.delete(NilClass)
127   return unless classes.size == 1
128 
129   klass = classes[0]
130   if klass == Integer
131     # This branch is not taken on Ruby <2.4, because of the Fixnum/Bignum split.
132     # However, that causes no problems as pg_auto_parameterize handles integer
133     # arrays natively (though the SQL used is different)
134     "int8"
135   elsif klass == String
136     _bound_variable_type_for_string_array(r)
137   elsif klass == BigDecimal
138     "numeric"
139   elsif klass == Date
140     "date"
141   elsif klass == Time
142     @db.cast_type_literal(Time)
143   elsif klass == Float
144     # PostgreSQL treats literal floats as numeric, not double precision
145     # But older versions of PostgreSQL don't handle Infinity/NaN in numeric
146     r.all?{|v| v.nil? || v.finite?} ? "numeric" : "double precision"
147   elsif klass == Sequel::SQLTime
148     "time"
149   elsif klass == DateTime
150     @db.cast_type_literal(DateTime)
151   elsif klass == Sequel::SQL::Blob
152     "bytea"
153   end
154 end
_bound_variable_type_for_string_array(r) click to toggle source

Do not auto parameterize string arrays by default.

    # File lib/sequel/extensions/pg_auto_parameterize_in_array.rb
157 def _bound_variable_type_for_string_array(r)
158   nil
159 end
_convert_array_to_pg_array_with_type(r, type) click to toggle source

Convert RHS of IN/NOT IN operator to PGArray with given type.

    # File lib/sequel/extensions/pg_auto_parameterize_in_array.rb
167 def _convert_array_to_pg_array_with_type(r, type)
168   Sequel.pg_array(r, type)
169 end
pg_auto_parameterize_min_array_size() click to toggle source

The minimium size of array to auto parameterize.

    # File lib/sequel/extensions/pg_auto_parameterize_in_array.rb
162 def pg_auto_parameterize_min_array_size
163   2
164 end