class SqlPostgres::Insert

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

**

Public Class Methods

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

Create an insert statement

table

The table name

connection

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

# File lib/sqlpostgres/Insert.rb, line 25
def initialize(table, connection = Connection.default)
  @table = table
  @connection = connection
  @columns = []
  @values = []
  @query = nil
end

Public Instance Methods

default_values() click to toggle source
insert default values

Example:

** Example: insert_default_values

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

**

# File lib/sqlpostgres/Insert.rb, line 231
def default_values
  @query = "default values"
end
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/Insert.rb, line 253
def exec(connection = @connection)
  connection.exec(statement)
end
insert(column, value = :no_value) click to toggle source
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)"

**

# 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) click to toggle source

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.

# 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) click to toggle source
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')"

**

# 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) click to toggle source
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\"}')"

**

# 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) click to toggle source
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 **

# 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) click to toggle source
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
# 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) click to toggle source
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"

**

# File lib/sqlpostgres/Insert.rb, line 196
def select(select)
  @query = select.statement
end
statement() click to toggle source

Return the SQL statement. Especially useful for debugging.

# File lib/sqlpostgres/Insert.rb, line 237
def statement
  [
    "insert into",
    @table,
    column_list,
    query_expression,
  ].compact.join(' ')
end

Private Instance Methods

column_list() click to toggle source
# File lib/sqlpostgres/Insert.rb, line 263
def column_list
  "(#{@columns.join(', ')})" unless @columns.empty?
end
query_expression() click to toggle source
# File lib/sqlpostgres/Insert.rb, line 259
def query_expression
  @query || source
end
source() click to toggle source
# File lib/sqlpostgres/Insert.rb, line 267
def source
  "values (#{@values.join(', ')}) #{@returning_expression}".strip
end