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