Class: SqlPostgres::Update

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

Overview

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 **

Instance Method Summary collapse

Constructor Details

#initialize(table, connection = Connection.default) ⇒ Update

Create an update statement.

[table] The table name [connection] The connection to use. If nil, use the default connection instead.



22
23
24
25
26
27
28
 
# File 'lib/sqlpostgres/Update.rb', line 22

def initialize(table, connection = Connection.default)
  @table = table
  @connection = connection
  @set_clauses = []
  @conditions = []
  @only = false
end

Instance Method Details

#exec(connection = @connection) ⇒ Object

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.



154
155
156
 
# File 'lib/sqlpostgres/Update.rb', line 154

def exec(connection = @connection)
  connection.exec(statement)
end

#only ⇒ Object

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



41
42
43
 
# File 'lib/sqlpostgres/Update.rb', line 41

def only
  @only = true
end

#set(column, value) ⇒ Object

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



81
82
83
 
# File 'lib/sqlpostgres/Update.rb', line 81

def set(column, value)
  @set_clauses << [column, Translate.escape_sql(value)].join(' = ')
end

#set_array(column, value) ⇒ Object

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



120
121
122
 
# 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) ⇒ Object

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



101
102
103
 
# File 'lib/sqlpostgres/Update.rb', line 101

def set_bytea(column, value)
  @set_clauses << [column, Translate.escape_bytea(value, @connection.pgconn)].join(' = ')
end

#statement ⇒ Object

Return the SQL statement. Especially useful for debugging.



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

def statement
  "update#{only_option} #{@table} set #{set_clause_list}#{where_clause}"
end

#where(condition) ⇒ Object

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



137
138
139
 
# File 'lib/sqlpostgres/Update.rb', line 137

def where(condition)
  @conditions << Translate.substitute_values(condition)
end