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

new(connection = Connection.default) click to toggle source

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

cross_join(table) click to toggle source
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
distinct() click to toggle source
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
distinct_on(expression) click to toggle source
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
except(select) click to toggle source

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
except_all(select) click to toggle source

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
exec(connection = @connection) click to toggle source

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_by_cursor(cursor_name, direction, connection = @connection) click to toggle source

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
for_update() click to toggle source
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
from(table, as = nil) click to toggle source
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
group_by(expression) click to toggle source
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
having(expression) click to toggle source
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
intersect(select) click to toggle source

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
intersect_all(select) click to toggle source

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
join_on(joinType, table, condition) click to toggle source
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
join_using(joinType, table, *columns) click to toggle source
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
limit(value) click to toggle source
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
natural_join(table) click to toggle source
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
offset(value) click to toggle source
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
order_by(expression, ordering = nil) click to toggle source
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
select(expression, as = nil, &converter) click to toggle source
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_literal(value, as = nil) click to toggle source
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
statement() click to toggle source

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
union(select) click to toggle source
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
union_all(select) click to toggle source

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
where(expression) click to toggle source
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

add_set_op(op, select) click to toggle source
# File lib/sqlpostgres/Select.rb, line 745
def add_set_op(op, select)
  @set_ops << [op, select.statement]
end
data_type_for(value, as) click to toggle source
# 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
distinct_clause() click to toggle source
# 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
expression_list() click to toggle source
# File lib/sqlpostgres/Select.rb, line 749
def expression_list
  @columns.collect do |column|
    [column.value, column.as].compact.join(' as ')
  end.join(', ')
end
for_update_clause() click to toggle source
# File lib/sqlpostgres/Select.rb, line 811
def for_update_clause
  if @for_update
    " for update"
  else
    ""
  end
end
group_by_clause() click to toggle source
# File lib/sqlpostgres/Select.rb, line 827
def group_by_clause
  if @group_by.empty?
    ""
  else
    " group by " + @group_by.join(', ')
  end
end
having_clause() click to toggle source
# File lib/sqlpostgres/Select.rb, line 835
def having_clause
  if @having.empty?
    ""
  else
    " having " + @having.join(' and ')
  end
end
join_clause() click to toggle source
# File lib/sqlpostgres/Select.rb, line 763
def join_clause
  if @joins.empty?
    ""
  else
    " " + @joins.join(' ')
  end
end
limit_clause() click to toggle source
# File lib/sqlpostgres/Select.rb, line 795
def limit_clause
  if @limit.nil?
    ""
  else
    " limit #{@limit}"
  end
end
offset_clause() click to toggle source
# File lib/sqlpostgres/Select.rb, line 803
def offset_clause
  if @offset.nil?
    ""
  else
    " offset #{@offset}"
  end
end
order_by_clause() click to toggle source
# 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
query_and_translate(connection, statement) click to toggle source
# File lib/sqlpostgres/Select.rb, line 843
def query_and_translate(connection, statement)
  connection.exec_and_translate(statement, @columns)
end
random_order?() click to toggle source
# File lib/sqlpostgres/Select.rb, line 781
def random_order?
  ENV['RANDOM_SQL_ORDER'] && !@distinct && @distinct_on.empty? && @set_ops.empty?
end
set_ops_clause() click to toggle source
# 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
tableExpression() click to toggle source
# File lib/sqlpostgres/Select.rb, line 755
def tableExpression
  if @tables.empty?
    ""
  else
    " from #{@tables.join(', ')}"
  end
end
where_clause() click to toggle source
# File lib/sqlpostgres/Select.rb, line 819
def where_clause
  if @where.empty?
    ""
  else
    " where " + @where.join(' and ')
  end
end