class SqlPostgres::Select
This class creates and executes an SQL select statement. Example (assuming the values 1, 2 and null are in the database):
** Example: select
select = Select.new(connection) select.select('i') select.from('foo') select.order_by('i') p select.statement # "select i from foo order by i" p select.exec # [{"i"=>1}, {"i"=>2}, {"i"=>nil}]
**
Constants
- ARRAY_ELEMENT_TYPES
Map each array type to its base type.
- AutoConverter
- BitConverter
Converters used to translate strings into Ruby types.
- BooleanConverter
- BoxConverter
- ByteaConverter
- CONVERTERS
Map each base (non-array) type to a converter.
- CidrConverter
- CircleConverter
- Column
- DateConverter
- FloatConverter
- InetConverter
- IntegerConverter
- IntervalConverter
- LsegConverter
- MacAddrConverter
- PathConverter
- PointConverter
- PolygonConverter
- QCharConverter
- StringConverter
- TimeConverter
- TimeStringConverter
- TimeWithTimeZoneConverter
- TimestampConverter
- TimestampTzConverter
Public Class Methods
Constructor. If no connection is given, uses the default.
# File lib/sqlpostgres/Select.rb, line 22 def initialize(connection = Connection.default) @connection = connection @tables = [] @columns = [] @joins = [] @order_by = [] @where = [] @group_by = [] @having = [] @limit = nil @offset = nil @distinct = false @distinct_on = [] @set_ops = [] @for_update = false end
Public Instance Methods
Add a "cross join" to this statement. Example:
** Example: select_cross_join
select = Select.new select.select('i') select.from('foo') select.cross_join('bar') p select.statement # "select i from foo cross join bar"
**
# File lib/sqlpostgres/Select.rb, line 338 def cross_join(table) @joins << "cross join #{table}" end
Add "distinct" to this statement. Example:
** Example: select_distinct
select = Select.new select.distinct select.select('i') select.from('foo') p select.statement # "select distinct i from foo"
**
# File lib/sqlpostgres/Select.rb, line 143 def distinct @distinct = true end
Add "distinct on" to this statement. "distinct on" is a postgres extension. Example:
** Example: select_distinct_on
select = Select.new select.distinct_on('i') select.select('integer', 'i') select.select('integer', 'j') select.from('foo') p select.statement # "select distinct on (i) i, j from # foo"
**
# File lib/sqlpostgres/Select.rb, line 161 def distinct_on(expression) @distinct_on << expression end
Add the “except” set operation to this statement. See union
.
# File lib/sqlpostgres/Select.rb, line 246 def except(select) add_set_op('except', select) end
Add the “except all” set operation to this statement. See union
.
# File lib/sqlpostgres/Select.rb, line 253 def except_all(select) add_set_op('except all', select) end
Execute the statement and return an array of hashes with the result.
- connection
-
If present, the connection to use. If nil, uses the connection passed to new or, if no connection was passed to new, uses the default connection.
# File lib/sqlpostgres/Select.rb, line 516 def exec(connection = @connection) query_and_translate(connection, statement) end
Fetch a row or rows from the cursor. Not intended for consumer use; it's hanging out here in public for the use of the Cursor
class.
- cursor_name
-
The cursor's name
- direction
-
A string specifying which row or rows to fetch (see
Cursor.fetch
) - connection
-
If present, the connection to use. If nil, uses the connection passed to new or, if no connection was passed to new, uses the default connection.
# File lib/sqlpostgres/Select.rb, line 533 def fetch_by_cursor(cursor_name, direction, connection = @connection) statement = "fetch #{direction} from #{cursor_name}" query_and_translate(connection, statement) end
Add "for update" to the statement. Example:
** Example: select_for_update
select = Select.new select.select('i') select.from('foo') select.for_update p select.statement # "select i from foo for update"
**
# File lib/sqlpostgres/Select.rb, line 496 def for_update @for_update = true end
Add the "from" clause to the statement. [table] What's being selected from, which is either * A table name, or * a Select statement [as] The alias name, or nil if there isn't one Table example:
** Example: select_from
select = Select.new select.select('i') select.from('foo') p select.statement # "select i from foo"
**
Subselect example:
** Example: select_from_subselect
subselect = Select.new subselect.select('i') subselect.from('foo') select = Select.new select.select('i') select.from(subselect, 'bar') p select.statement # "select i from (select i from foo) as bar"
**
# File lib/sqlpostgres/Select.rb, line 193 def from(table, as = nil) table = "(#{table.statement})" if table.is_a?(Select) @tables << [table, as].compact.join(' as ') end
Add a "group by" to this statement. [expression] A string or array that will be converted by #substitute_values and inserted into the statement. Example
** Example: select_group_by
select = Select.new select.select('i') select.select('count(*)', 'count') select.from('foo') select.group_by('i') p select.statement # "select i, count(*) as count from foo group # by i"
**
# File lib/sqlpostgres/Select.rb, line 427 def group_by(expression) @group_by << Translate.substitute_values(expression) end
Add a "having" clause to this statement. [expression] A string or array that will be converted by #substitute_values and inserted into the statement. Example
** Example: select_having
select = Select.new select.select('i') select.select('count(*)', 'count') select.from('foo') select.group_by('i') select.having('i < 10') p select.statement # "select i, count(*) as count from foo # group by i having i < 10"
**
# File lib/sqlpostgres/Select.rb, line 449 def having(expression) @having << Translate.substitute_values(expression) end
Add the “intersect” set operation to this statement. See union
.
# File lib/sqlpostgres/Select.rb, line 232 def intersect(select) add_set_op('intersect', select) end
Add the “intersect all” set operation to this statement. See union
.
# File lib/sqlpostgres/Select.rb, line 239 def intersect_all(select) add_set_op('intersect all', select) end
Add a "join on" to this statement. [joinType] One of: * 'inner' * 'left outer' * 'right outer' * 'full outer' [table] The table being joined [condition] A string or array that will be converted by #substitute_values and inserted into the statement. Example:
** Example: select_join_on
select = Select.new select.select('i') select.from('foo') select.join_on('inner', 'bar', 'foo.i = bar.j') p select.statement # "select i from foo inner join bar on (foo.i = # bar.j)"
**
# File lib/sqlpostgres/Select.rb, line 322 def join_on(joinType, table, condition) @joins << ("#{joinType} join #{table} on "\ "(#{Translate.substitute_values(condition)})") end
Add a "join using" to this statement. [joinType] One of: * 'inner' * 'left outer' * 'right outer' * 'full outer' [table] The table being joined [*columns] One or more column names. Example:
** Example: select_join_using
select = Select.new select.select('i') select.from('foo') select.join_using('inner', 'bar', 'i', 'j') p select.statement # "select i from foo inner join bar using (i, j)"
**
# File lib/sqlpostgres/Select.rb, line 294 def join_using(joinType, table, *columns) @joins << "#{joinType} join #{table} using (#{columns.join(', ')})" end
Add a "limit" clause to the statement. Example:
** Example: select_limit
select = Select.new select.select('i') select.from('foo') select.order_by('i') select.limit(1) p select.statement # "select i from foo order by i limit 1"
**
# File lib/sqlpostgres/Select.rb, line 465 def limit(value) @limit = value end
Add a natural join to this statement. Example:
** Example: select_natural_join
select = Select.new select.select('i') select.from('foo') select.natural_join('bar') p select.statement # "select i from foo natural join bar"
**
# File lib/sqlpostgres/Select.rb, line 268 def natural_join(table) @joins << "natural join #{table}" end
Add an "offset" clause to the statement. Example:
** Example: select_offset
select = Select.new select.select('i') select.from('foo') select.order_by('i') select.offset(1) p select.statement # "select i from foo order by i offset 1"
**
# File lib/sqlpostgres/Select.rb, line 481 def offset(value) @offset = value end
Add an "order by" to this statement. You can call this as many times as you need. [expression] A string or array that will be converted by #substitute_values and inserted into the statement. [ordering] One of: 'asc':: ascending 'desc':: descending nil:: default ordering, which is ascending Example:
** Example: select_order_by
select = Select.new select.select('i') select.select('j') select.from('foo') select.order_by('i') select.order_by('j', 'desc') p select.statement # "select i, j from foo order by i, j desc"
**
# File lib/sqlpostgres/Select.rb, line 365 def order_by(expression, ordering = nil) @order_by << [Translate.substitute_values(expression), ordering].compact.join(' ') end
Add an expression (usually just a simple column name) to the select statement. [expression] The expression to put in the select statement. Should be one of: [An instance of Select] The Select's SQL statement is put in parentheses and added to this statement. [String or Array] Converted by #substitute_values [as] The alias name to put in the statement and to use as the hash key in the result. If nil, then no alias name appears in the statement and the expression is used as the hash key. Example:
** Example: select_select
select = Select.new(connection) select.select('i') select.from('foo') p select.statement # "select i from foo" p select.exec # [{"i"=>1}]
**
Example (alias)
** Example: select_select_alias
select = Select.new(connection) select.select('i', 'number') select.from('foo') p select.statement # "select i as number from foo" p select.exec # [{"number"=>1}]
**
Example (expression)
** Example: select_select_expression
pi = 3.14 select = Select.new(connection) select.select(['d * %s', pi], 'c') select.from('circles') p select.statement # "select d * 3.14 as c from circles" p select.exec # [{"c"=>6.28}]
**
# File lib/sqlpostgres/Select.rb, line 80 def select(expression, as = nil, &converter) converter ||= AutoConverter expression = if expression.is_a?(Select) "(#{expression.statement})" else Translate.substitute_values(expression) end @columns << Column.new(expression, as, converter) end
Select a literal, automatically selecting its result type. [value] The value to put in the statement. This can be any of these types: * nil * Integer * Float * String * true or false * #PgTime * #PgInterval * #PgTimeWithTimeZone * #PgTimestamp * #PgPoint * #PgLineSegment * #PgBox * #PgPath * #PgPolygon * #PgCircle * #PgBit * #PgInet * #PgCidr * #PgMacAddr [as] The alias name to put in the statement and to use as the hash key in the result. If nil, then no alias name appears in the statement and the value itself is used as the hash key. Example:
** Example: select_select_literal
select = Select.new(connection) select.select_literal(2, 'n') select.select_literal('foo', 't') p select.statement # "select 2 as n, E'foo' as t" p select.exec # [{"n"=>2, "t"=>"foo"}]
**
# File lib/sqlpostgres/Select.rb, line 128 def select_literal(value, as = nil) select(["%s", value], as) end
Return the SQL statement.
# File lib/sqlpostgres/Select.rb, line 502 def statement "select#{distinct_clause} #{expression_list}"\ "#{tableExpression}#{join_clause}"\ "#{where_clause}#{set_ops_clause}#{group_by_clause}#{having_clause}#{order_by_clause}"\ "#{limit_clause}#{offset_clause}#{for_update_clause}" end
Add the "union" set operation to this statement. You may call this multiple times. The right-hand-side of the union is put in parentheses. This makes it possible to force the order when doing multiple set operations. Example
** Example: select_union
select2 = Select.new select2.select('i') select2.from('bar') select1 = Select.new select1.select('i') select1.from('foo') select1.union(select2) p select1.statement # "select i from foo union (select i from # bar)"
**
# File lib/sqlpostgres/Select.rb, line 218 def union(select) add_set_op('union', select) end
Add the “union all” set operation to this statement. See union
.
# File lib/sqlpostgres/Select.rb, line 225 def union_all(select) add_set_op('union all', select) end
Add a "where" condition to this statement. [expression] The condition. Should be one of: [A string] The expression [An array] An expression converted using #substitute_values Example (string)
** Example: select_where_string
select = Select.new select.select('i') select.from('foo') select.where('i > 0') p select.statement # "select i from foo where i > 0"
**
Example (array)
** Example: select_where_array
select = Select.new select.select('age') select.from('person') select.where(['name = %s', 'Smith']) p select.statement # "select age from person where name = # E'Smith'"
**
Example (in)
** Example: select_where_in
select = Select.new select.select('s') select.from('foo') select.where(['s in %s', [:in, 'foo', 'bar']]) p select.statement # "select s from foo where s in (E'foo', # E'bar')"
**
# File lib/sqlpostgres/Select.rb, line 406 def where(expression) @where << Translate.substitute_values(expression) end
Private Instance Methods
# File lib/sqlpostgres/Select.rb, line 745 def add_set_op(op, select) @set_ops << [op, select.statement] end
# File lib/sqlpostgres/Select.rb, line 720 def data_type_for(value, as) return nil if as.nil? if value.is_a?(Float) 'float' elsif value.is_a?(Integer) 'integer' elsif value == true || value == false 'boolean' elsif value.is_a?(Time) 'time' else 'string' end end
# File lib/sqlpostgres/Select.rb, line 735 def distinct_clause if @distinct " distinct" elsif !@distinct_on.empty? " distinct on (#{@distinct_on.join(', ')})" else "" end end
# File lib/sqlpostgres/Select.rb, line 749 def expression_list @columns.collect do |column| [column.value, column.as].compact.join(' as ') end.join(', ') end
# File lib/sqlpostgres/Select.rb, line 811 def for_update_clause if @for_update " for update" else "" end end
# File lib/sqlpostgres/Select.rb, line 827 def group_by_clause if @group_by.empty? "" else " group by " + @group_by.join(', ') end end
# File lib/sqlpostgres/Select.rb, line 835 def having_clause if @having.empty? "" else " having " + @having.join(' and ') end end
# File lib/sqlpostgres/Select.rb, line 763 def join_clause if @joins.empty? "" else " " + @joins.join(' ') end end
# File lib/sqlpostgres/Select.rb, line 795 def limit_clause if @limit.nil? "" else " limit #{@limit}" end end
# File lib/sqlpostgres/Select.rb, line 803 def offset_clause if @offset.nil? "" else " offset #{@offset}" end end
# File lib/sqlpostgres/Select.rb, line 785 def order_by_clause order = @order_by.dup order << 'random()' if random_order? if order.empty? "" else " order by " + order.join(', ') end end
# File lib/sqlpostgres/Select.rb, line 843 def query_and_translate(connection, statement) connection.exec_and_translate(statement, @columns) end
# File lib/sqlpostgres/Select.rb, line 781 def random_order? ENV['RANDOM_SQL_ORDER'] && !@distinct && @distinct_on.empty? && @set_ops.empty? end
# File lib/sqlpostgres/Select.rb, line 771 def set_ops_clause if @set_ops.empty? "" else ' ' + @set_ops.collect do |op, statement| "#{op} (#{statement})" end.join(' ') end end
# File lib/sqlpostgres/Select.rb, line 755 def tableExpression if @tables.empty? "" else " from #{@tables.join(', ')}" end end
# File lib/sqlpostgres/Select.rb, line 819 def where_clause if @where.empty? "" else " where " + @where.join(' and ') end end