class SqlPostgres::Update

This class creates and executes an SQL update statement.

Example:

** Example: update

update = Update.new('foo', connection)
update.set('i', 2)
p update.statement    # "update foo set i = 2"
update.exec

**

Public Class Methods

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

Create an update statement.

table

The table name

connection

The connection to use. If nil, use the default connection instead.

# File lib/sqlpostgres/Update.rb, line 22
def initialize(table, connection = Connection.default)
  @table = table
  @connection = connection
  @set_clauses = []
  @conditions = []
  @only = false
end

Public Instance Methods

exec(connection = @connection) click to toggle source

Execute the statement.

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/Update.rb, line 154
def exec(connection = @connection)
  connection.exec(statement)
end
only() click to toggle source
Add "only" to this statement.  This is a postgres extension
which causes the update to *not* apply to derived tables.

Example:

** Example: update_only

update = Update.new('foo')
update.only
update.set('i', 0)
p update.statement    # "update only foo set i = 0"

**

# File lib/sqlpostgres/Update.rb, line 41
def only
  @only = true
end
set(column, value) click to toggle source
Set a column to a value.

[column]
  The column name
[value]
  The value to set the column to.  Ruby data types are converted
  to SQL automatically using #escape_sql.

Example showing a few different types:

** Example: update_set

update = Update.new('foo')
update.set('name', 'Fred')
update.set('hire_date', Time.local(2002, 1, 1))
p update.statement      # "update foo set name = E'Fred', hire_date =
                        # timestamp '2002-01-01 00:00:00.000000'"

**

Example showing a subselect:

** Example: update_set_subselect

select = Select.new
select.select('j')
select.from('bar')
select.where(["i = foo.i"])
update = Update.new('foo')
update.set('i', select)
p update.statement         # "update foo set i = (select j from bar
                           # where i = foo.i)"

**

Example showing an expression:

** Example: update_set_expression

update = Update.new('foo')
update.set('i', ['i + 1'])
p update.statement           # "update foo set i = i + 1"

**

# File lib/sqlpostgres/Update.rb, line 81
def set(column, value)
  @set_clauses << [column, Translate.escape_sql(value)].join(' = ')
end
set_array(column, value) click to toggle source
Set a column to an array.

[column]
  The column name
[value]
  The value to set the column to.  Ruby data types are converted
  to SQL automatically using #escape_array.

Example:

** Example: update_set_array

update = Update.new('foo')
update.set_array('i', [1, 2, 3])
p update.statement      # "update foo set i = ARRAY[1, 2, 3]"

**

# File lib/sqlpostgres/Update.rb, line 120
def set_array(column, value)
  @set_clauses << [column, Translate.escape_array(value)].join(' = ')
end
set_bytea(column, value) click to toggle source
Set a bytea column.  You must use this function, not #set, when
updating a bytea column.  That's because bytea columns need
special escaping.

[column]
  The column name
[value]
  The value to add.

Example:

** Example: update_set_bytea

update = Update.new('foo')
update.set_bytea('name', "\000\377")
p update.statement      # "update foo set name = E'\\\\000\\\\377'"

**

# File lib/sqlpostgres/Update.rb, line 101
def set_bytea(column, value)
  @set_clauses << [column, Translate.escape_bytea(value, @connection.pgconn)].join(' = ')
end
statement() click to toggle source

Return the SQL statement. Especially useful for debugging.

# File lib/sqlpostgres/Update.rb, line 143
def statement
  "update#{only_option} #{@table} set #{set_clause_list}#{where_clause}"
end
where(condition) click to toggle source
Add a where clause to the statement.

[expression]
  A string or array, converted using #substitute_values

Example:

** Example: update_where

update = Update.new('foo')
update.set('i', 1)
update.where(['t = %s', "bar"])
p update.statement     # "update foo set i = 1 where t = E'bar'"

**

# File lib/sqlpostgres/Update.rb, line 137
def where(condition)
  @conditions << Translate.substitute_values(condition)
end

Private Instance Methods

only_option() click to toggle source
# File lib/sqlpostgres/Update.rb, line 172
def only_option
  if @only
    " only"
  else
    ""
  end
end
set_clause_list() click to toggle source
# File lib/sqlpostgres/Update.rb, line 160
def set_clause_list
  @set_clauses.join(', ')
end
where_clause() click to toggle source
# File lib/sqlpostgres/Update.rb, line 164
def where_clause
  if @conditions.empty?
    ""
  else
    " where #{@conditions.join(' and ')}"
  end
end