Class: SqlPostgres::Insert

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

Overview

This class creates and executes an SQL insert statement.

Example: ** Example: insert

insert = Insert.new('foo', connection)
insert.insert('i', 1)
insert.insert('t', 'foo')
p insert.statement           # "insert into foo (i, t) values (1, 
                             # E'foo')"
insert.exec

**

Instance Method Summary collapse

Constructor Details

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

Create an insert statement

table

The table name

connection

If supplied, the connection to use. If not supplied, use the default.



25
26
27
28
29
30
31
 
# File 'lib/sqlpostgres/Insert.rb', line 25

def initialize(table, connection = Connection.default)
  @table = table
  @connection = connection
  @columns = []
  @values = []
  @query = nil
end

Instance Method Details

#default_values ⇒ Object

insert default values

Example: ** Example: insert_default_values

insert = Insert.new('foo')
insert.default_values
p insert.statement     # "insert into foo default values"

**



231
232
233
 
# File 'lib/sqlpostgres/Insert.rb', line 231

def default_values
  @query = "default values"
end

#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.



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

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

#insert(column, value = :no_value) ⇒ Object

Add a column to the statement. This is for all column types except bytea.

column

The column name

value

The value to add. The value is SQL escaped. Should be one of:

  • a String

  • an Integer

  • a Float

  • a Time

  • false

  • true

  • nil

  • a Select

  • :default

  • :no_value

Special values:

a Select

The select’s SQL is added in parentheses

:default

Add the SQL keyword “default” to the statement.

:no_value

Do not add a value for this column. This is used when the values are being provided by a Select statement.

Example (simple) ** Example: insert_insert

insert = Insert.new('foo')
insert.insert('t', 'bar')
p insert.statement         # "insert into foo (t) values (E'bar')"

**

Example (select) ** Example: insert_insert_select

select = Select.new
select.select('j')
select.from('bar')
select.limit(1)
insert = Insert.new('foo')
insert.insert('i', select)
p insert.statement           # "insert into foo (i) values ((select j 
                             # from bar limit 1))"

**

Example (default) ** Example: insert_insert_default

insert = Insert.new('foo')
insert.insert('i', :default)
p insert.statement             # "insert into foo (i) values 
                               # (default)"

**



88
89
90
91
 
# File 'lib/sqlpostgres/Insert.rb', line 88

def insert(column, value = :no_value)
  @columns << column
  @values << Translate.escape_sql(value) unless value == :no_value
end

#insert_array(column, value) ⇒ Object

Insert into an array (int[], text[], etc) column. This is not for byte array (bytea) column types: For that, call #insert_bytea.

column

The column name

value

The value to add.

This is used for inserting literals and expressions. To insert the result of an SQL query, or to insert the default value, call #insert.



106
107
108
109
 
# File 'lib/sqlpostgres/Insert.rb', line 106

def insert_array(column, value)
  @columns << column
  @values << Translate.escape_array(value)
end

#insert_bytea(column, value = :no_value) ⇒ Object

Insert into a bytea column. You must use this function, not #insert, when inserting a string into a bytea column. That’s because bytea columns need special escaping.

column

The column name

value

The value to add. Should be one of:

  • a String

  • :default

  • :no_value

Special values:

a Select

The select’s SQL is added in parentheses

:default

Add the SQL keyword “default” to the statement.

:no_value

Do not add a value for this column. This is used when the values are being provided by a Select statement.

Example: ** Example: insert_bytea

insert = Insert.new('foo')
insert.insert_bytea('t', "\000\001\002\003")
p insert.statement     # "insert into foo (t) values 
                       # (E'\\\\000\\\\001\\\\002\\\\003')"

**



141
142
143
144
 
# File 'lib/sqlpostgres/Insert.rb', line 141

def insert_bytea(column, value = :no_value)
  @columns << column
  @values << Translate.escape_bytea(value, @connection.pgconn) unless value == :no_value
end

#insert_bytea_array(column, value = :no_value) ⇒ Object

Insert into a bytea[] (bytea array) column. You must use this function, not #insert or #insert_array, because bytea[] columns need special escaping.

** Example: insert_bytea_array

insert = Insert.new('foo')
insert.insert_bytea_array('t', ["foo", "\000bar\nbaz"])
p insert.statement     # "insert into foo (t) values 
                       # ('{\"foo\",\"\\\\\\\\000bar\nbaz\"}')"

**



158
159
160
161
 
# File 'lib/sqlpostgres/Insert.rb', line 158

def insert_bytea_array(column, value = :no_value)
  @columns << column
  @values << Translate.escape_bytea_array(value) unless value == :no_value
end

#insert_qchar(column, value = :no_value) ⇒ Object

Insert into a “char” column. This is a Postgres specific data type that is different than char or character (yes, the quotes are part of the type name). “char” values are escaped differently than normal test, so be sure to use this method and not #insert when inserting into a “char” column.

column

The column name

value

A string of length 1

Example: ** Example: insert_qchar **



178
179
180
181
 
# File 'lib/sqlpostgres/Insert.rb', line 178

def insert_qchar(column, value = :no_value)
  @columns << column
  @values << Translate.escape_qchar(value)
end

#returning(expression, name = nil) ⇒ Object

Define return clause

Example: (simple) ** Example: insert_returning

insert = Insert.new('foo')
insert.insert('i', 3)
insert.returning('i')
p insert.statement       # "insert into foo (i) values (3) returning i

Example: (expression_with_alias) ** Example: insert_returning_with_alias

insert = Insert.new('foo')
insert.insert('i', 3)
insert.returning('i*3', 'calc')
p insert.statement       # "insert into foo (i) values (3) returning i*3 as calc



216
217
218
219
220
 
# File 'lib/sqlpostgres/Insert.rb', line 216

def returning(expression, name=nil)
  str = "returning #{expression}"
  str += " as #{name}" if name
  @returning_expression = str
end

#select(select) ⇒ Object

Insert the results of a select statement

Example: ** Example: insert_select

select = Select.new
select.select('i')
select.from('bar')
insert = Insert.new('foo')
insert.insert('i')
insert.select(select)
p insert.statement     # "insert into foo (i) select i from bar"

**



196
197
198
 
# File 'lib/sqlpostgres/Insert.rb', line 196

def select(select)
  @query = select.statement
end

#statement ⇒ Object

Return the SQL statement. Especially useful for debugging.



237
238
239
240
241
242
243
244
 
# File 'lib/sqlpostgres/Insert.rb', line 237

def statement
  [
    "insert into",
    @table,
    column_list,
    query_expression,
  ].compact.join(' ')
end