Class: SqlPostgres::Select

Inherits:
Object
  • Object
show all
Defined in:
lib/sqlpostgres/Select.rb

Overview

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}]

**

Defined Under Namespace

Modules: Types Classes: Column

Instance Method Summary collapse

Constructor Details

#initialize(connection = Connection.default) ⇒ Select

Constructor. If no connection is given, uses the default.



22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
# 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

Instance Method Details

#cross_join(table) ⇒ Object

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"

**



338
339
340
 
# File 'lib/sqlpostgres/Select.rb', line 338

def cross_join(table)
  @joins << "cross join #{table}"
end

#distinct ⇒ Object

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"

**



143
144
145
 
# File 'lib/sqlpostgres/Select.rb', line 143

def distinct
  @distinct = true
end

#distinct_on(expression) ⇒ Object

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"

**



161
162
163
 
# File 'lib/sqlpostgres/Select.rb', line 161

def distinct_on(expression)
  @distinct_on << expression
end

#except(select) ⇒ Object

Add the “except” set operation to this statement. See #union.



246
247
248
 
# File 'lib/sqlpostgres/Select.rb', line 246

def except(select)
  add_set_op('except', select)
end

#except_all(select) ⇒ Object

Add the “except all” set operation to this statement. See #union.



253
254
255
 
# File 'lib/sqlpostgres/Select.rb', line 253

def except_all(select)
  add_set_op('except all', select)
end

#exec(connection = @connection) ⇒ Object

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.



516
517
518
 
# 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) ⇒ Object

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.



533
534
535
536
 
# 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 ⇒ Object

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"

**



496
497
498
 
# File 'lib/sqlpostgres/Select.rb', line 496

def for_update
  @for_update = true
end

#from(table, as = nil) ⇒ Object

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"

**



193
194
195
196
 
# 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) ⇒ Object

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"

**



427
428
429
 
# File 'lib/sqlpostgres/Select.rb', line 427

def group_by(expression)
  @group_by << Translate.substitute_values(expression)
end

#having(expression) ⇒ Object

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"

**



449
450
451
 
# File 'lib/sqlpostgres/Select.rb', line 449

def having(expression)
  @having << Translate.substitute_values(expression)
end

#intersect(select) ⇒ Object

Add the “intersect” set operation to this statement. See #union.



232
233
234
 
# File 'lib/sqlpostgres/Select.rb', line 232

def intersect(select)
  add_set_op('intersect', select)
end

#intersect_all(select) ⇒ Object

Add the “intersect all” set operation to this statement. See #union.



239
240
241
 
# File 'lib/sqlpostgres/Select.rb', line 239

def intersect_all(select)
  add_set_op('intersect all', select)
end

#join_on(joinType, table, condition) ⇒ Object

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)"

**



322
323
324
325
 
# 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) ⇒ Object

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)"

**



294
295
296
 
# File 'lib/sqlpostgres/Select.rb', line 294

def join_using(joinType, table, *columns)
  @joins << "#{joinType} join #{table} using (#{columns.join(', ')})"
end

#limit(value) ⇒ Object

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"

**



465
466
467
 
# File 'lib/sqlpostgres/Select.rb', line 465

def limit(value)
  @limit = value
end

#natural_join(table) ⇒ Object

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"

**



268
269
270
 
# File 'lib/sqlpostgres/Select.rb', line 268

def natural_join(table)
  @joins << "natural join #{table}"
end

#offset(value) ⇒ Object

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"

**



481
482
483
 
# File 'lib/sqlpostgres/Select.rb', line 481

def offset(value)
  @offset = value
end

#order_by(expression, ordering = nil) ⇒ Object

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"

**



365
366
367
368
 
# 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) ⇒ Object

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}]

**



80
81
82
83
84
85
86
87
88
 
# 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) ⇒ Object

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"}]

**



128
129
130
 
# File 'lib/sqlpostgres/Select.rb', line 128

def select_literal(value, as = nil)
  select(["%s", value], as)
end

#statement ⇒ Object

Return the SQL statement.



502
503
504
505
506
507
 
# 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) ⇒ Object

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)"

**



218
219
220
 
# File 'lib/sqlpostgres/Select.rb', line 218

def union(select)
  add_set_op('union', select)
end

#union_all(select) ⇒ Object

Add the “union all” set operation to this statement. See #union.



225
226
227
 
# File 'lib/sqlpostgres/Select.rb', line 225

def union_all(select)
  add_set_op('union all', select)
end

#where(expression) ⇒ Object

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')"

**



406
407
408
 
# File 'lib/sqlpostgres/Select.rb', line 406

def where(expression)
  @where << Translate.substitute_values(expression)
end