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