module Sequel::Postgres::DatasetMethods

Instance methods for datasets that connect to a PostgreSQL database.

Constants

ACCESS_EXCLUSIVE
ACCESS_SHARE
APOS
APOS_RE
AS
BACKSLASH
BLOB_RE
BOOL_FALSE
BOOL_TRUE
COMMA
COMMA_SEPARATOR
CRLF
DELETE_CLAUSE_METHODS
DELETE_CLAUSE_METHODS_91
DOUBLE_APOS
EMPTY_STRING
ESCAPE
EXCLUSIVE
EXPLAIN
EXPLAIN_ANALYZE
FOR_SHARE
FROM
INSERT_CLAUSE_METHODS
INSERT_CLAUSE_METHODS_91
LOCK_MODES
NULL
PAREN_CLOSE
PAREN_OPEN
PG_TIMESTAMP_FORMAT
QUERY_PLAN
ROW_EXCLUSIVE
ROW_SHARE
SELECT_CLAUSE_METHODS
SELECT_CLAUSE_METHODS_84
SHARE
SHARE_ROW_EXCLUSIVE
SHARE_UPDATE_EXCLUSIVE
SPACE
SQL_WITH_RECURSIVE
UPDATE_CLAUSE_METHODS
UPDATE_CLAUSE_METHODS_91
WINDOW
XOR_OP

Public Instance Methods

analyze() click to toggle source

Return the results of an EXPLAIN ANALYZE query as a string

# File lib/sequel/adapters/shared/postgres.rb, line 1105
def analyze
  explain(:analyze=>true)
end
complex_expression_sql_append(sql, op, args) click to toggle source

Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1112
def complex_expression_sql_append(sql, op, args)
  case op
  when :^
    j = XOR_OP
    c = false
    args.each do |a|
      sql << j if c
      literal_append(sql, a)
      c ||= true
    end
  when :ILIKE, :'NOT ILIKE'
    sql << PAREN_OPEN
    literal_append(sql, args.at(0))
    sql << SPACE << op.to_s << SPACE
    literal_append(sql, args.at(1))
    sql << ESCAPE
    literal_append(sql, BACKSLASH)
    sql << PAREN_CLOSE
  else
    super
  end
end
explain(opts={}) click to toggle source

Return the results of an EXPLAIN query as a string

# File lib/sequel/adapters/shared/postgres.rb, line 1136
def explain(opts={})
  with_sql((opts[:analyze] ? EXPLAIN_ANALYZE : EXPLAIN) + select_sql).map(QUERY_PLAN).join(CRLF)
end
for_share() click to toggle source

Return a cloned dataset which will use FOR SHARE to lock returned rows.

# File lib/sequel/adapters/shared/postgres.rb, line 1141
def for_share
  lock_style(:share)
end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1154
def insert(*values)
  if @opts[:returning]
    # already know which columns to return, let the standard code
    # handle it
    super
  elsif @opts[:sql]
    # raw SQL used, so don't know which table is being inserted
    # into, and therefore can't determine primary key.  Run the
    # insert statement and return nil.
    super
    nil
  else
    # Force the use of RETURNING with the primary key value.
    returning(insert_pk).insert(*values){|r| return r.values.first}
  end
end
insert_select(*values) click to toggle source

Insert a record returning the record inserted

# File lib/sequel/adapters/shared/postgres.rb, line 1172
def insert_select(*values)
  returning.insert(*values){|r| return r}
end
lock(mode, opts={}) { || ... } click to toggle source

Locks all tables in the dataset's FROM clause (but not in JOINs) with the specified mode (e.g. 'EXCLUSIVE'). If a block is given, starts a new transaction, locks the table, and yields. If a block is not given just locks the tables. Note that PostgreSQL will probably raise an error if you lock the table outside of an existing transaction. Returns nil.

# File lib/sequel/adapters/shared/postgres.rb, line 1181
def lock(mode, opts={})
  if block_given? # perform locking inside a transaction and yield to block
    @db.transaction(opts){lock(mode, opts); yield}
  else
    sql = 'LOCK TABLE '
    source_list_append(sql, @opts[:from])
    mode = mode.to_s.upcase.strip
    unless LOCK_MODES.include?(mode)
      Sequel::Deprecation.deprecate("Calling Dataset#lock with an unsupported lock mode will raise an Error in Sequel 4.")
    end
    sql << " IN #{mode} MODE"
    @db.execute(sql, opts)
  end
  nil
end
multi_insert_sql(columns, values) click to toggle source

PostgreSQL allows inserting multiple rows at once.

# File lib/sequel/adapters/shared/postgres.rb, line 1198
def multi_insert_sql(columns, values)
  sql = LiteralString.new('VALUES ')
  expression_list_append(sql, values.map{|r| Array(r)})
  [insert_sql(columns, sql)]
end
supports_cte_in_subqueries?() click to toggle source

PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).

# File lib/sequel/adapters/shared/postgres.rb, line 1206
def supports_cte_in_subqueries?
  supports_cte?
end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

# File lib/sequel/adapters/shared/postgres.rb, line 1211
def supports_distinct_on?
  true
end
supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

# File lib/sequel/adapters/shared/postgres.rb, line 1216
def supports_modifying_joins?
  true
end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

# File lib/sequel/adapters/shared/postgres.rb, line 1226
def supports_regexp?
  true
end
supports_returning?(type) click to toggle source

Returning is always supported.

# File lib/sequel/adapters/shared/postgres.rb, line 1221
def supports_returning?(type)
  true
end
supports_timestamp_timezones?() click to toggle source

PostgreSQL supports timezones in literal timestamps

# File lib/sequel/adapters/shared/postgres.rb, line 1231
def supports_timestamp_timezones?
  true
end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

# File lib/sequel/adapters/shared/postgres.rb, line 1236
def supports_window_functions?
  server_version >= 80400
end
truncate(opts = {}) click to toggle source

Truncates the dataset. Returns nil.

Options:

:cascade

whether to use the CASCADE option, useful when truncating

tables with Foreign Keys.
:only

truncate using ONLY, so child tables are unaffected

:restart

use RESTART IDENTITY to restart any related sequences

:only and :restart only work correctly on PostgreSQL 8.4+.

Usage:

DB[:table].truncate # TRUNCATE TABLE "table"
# => nil
DB[:table].truncate(:cascade => true, :only=>true, :restart=>true) # TRUNCATE TABLE ONLY "table" RESTART IDENTITY CASCADE
# => nil
Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1255
def truncate(opts = {})
  if opts.empty?
    super()
  else
    clone(:truncate_opts=>opts).truncate
  end
end
window(name, opts) click to toggle source

Return a clone of the dataset with an addition named window that can be referenced in window functions.

# File lib/sequel/adapters/shared/postgres.rb, line 1264
def window(name, opts)
  clone(:window=>(@opts[:window]||[]) + [[name, SQL::Window.new(opts)]])
end

Protected Instance Methods

_import(columns, values, opts={}) click to toggle source

If returned primary keys are requested, use RETURNING unless already set on the dataset. If RETURNING is already set, use existing returning values. If RETURNING is only set to return a single columns, return an array of just that column. Otherwise, return an array of hashes.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1274
def _import(columns, values, opts={})
  if @opts[:returning]
    statements = multi_insert_sql(columns, values)
    @db.transaction(opts.merge(:server=>@opts[:server])) do
      statements.map{|st| returning_fetch_rows(st)}
    end.first.map{|v| v.length == 1 ? v.values.first : v}
  elsif opts[:return] == :primary_key
    returning(insert_pk)._import(columns, values, opts)
  else
    super
  end
end

Private Instance Methods

_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

# File lib/sequel/adapters/shared/postgres.rb, line 1290
def _truncate_sql(table)
  to = @opts[:truncate_opts] || {}
  "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

# File lib/sequel/adapters/shared/postgres.rb, line 1296
def check_truncation_allowed!
  raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
  raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
end
compound_dataset_sql_append(sql, ds) click to toggle source

PostgreSQL requires parentheses around compound datasets if they use CTEs, and using them in other places doesn't hurt.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1389
def compound_dataset_sql_append(sql, ds)
  sql << PAREN_OPEN
  super
  sql << PAREN_CLOSE
end
delete_clause_methods() click to toggle source

PostgreSQL allows deleting from joined datasets

# File lib/sequel/adapters/shared/postgres.rb, line 1302
def delete_clause_methods
  if server_version >= 90100
    DELETE_CLAUSE_METHODS_91
  else
    DELETE_CLAUSE_METHODS
  end
end
delete_from_sql(sql) click to toggle source

Only include the primary table in the main delete clause

# File lib/sequel/adapters/shared/postgres.rb, line 1311
def delete_from_sql(sql)
  sql << FROM
  source_list_append(sql, @opts[:from][0..0])
end
delete_using_sql(sql) click to toggle source

Use USING to specify additional tables in a delete query

# File lib/sequel/adapters/shared/postgres.rb, line 1317
def delete_using_sql(sql)
  join_from_sql(:USING, sql)
end
full_text_string_join(cols) click to toggle source

Concatenate the expressions with a space in between

# File lib/sequel/adapters/shared/postgres.rb, line 1428
def full_text_string_join(cols)
  cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, EMPTY_STRING)}
  cols = cols.zip([SPACE] * cols.length).flatten
  cols.pop
  SQL::StringExpression.new(:'||', *cols)
end
insert_clause_methods() click to toggle source

PostgreSQL allows a RETURNING clause.

# File lib/sequel/adapters/shared/postgres.rb, line 1322
def insert_clause_methods
  if server_version >= 90100
    INSERT_CLAUSE_METHODS_91
  else
    INSERT_CLAUSE_METHODS
  end
end
insert_pk() click to toggle source

Return the primary key to use for RETURNING in an INSERT statement

# File lib/sequel/adapters/shared/postgres.rb, line 1331
def insert_pk
  if (f = opts[:from]) && !f.empty? && (pk = db.primary_key(f.first))
    Sequel::SQL::Identifier.new(pk)
  end
end
join_from_sql(type, sql) click to toggle source

For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.

# File lib/sequel/adapters/shared/postgres.rb, line 1339
def join_from_sql(type, sql)
  if(from = @opts[:from][1..-1]).empty?
    raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
  else
    sql << SPACE << type.to_s << SPACE
    source_list_append(sql, from)
    select_join_sql(sql)
  end
end
literal_blob_append(sql, v) click to toggle source

Use a generic blob quoting method, hopefully overridden in one of the subadapter methods

# File lib/sequel/adapters/shared/postgres.rb, line 1350
def literal_blob_append(sql, v)
  sql << APOS << v.gsub(BLOB_RE){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << APOS
end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

# File lib/sequel/adapters/shared/postgres.rb, line 1355
def literal_false
  BOOL_FALSE
end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1360
def literal_float(value)
  if value.finite?
    super
  elsif value.nan?
    "'NaN'"
  elsif value.infinite? == 1
    "'Infinity'"
  else
    "'-Infinity'"
  end
end
literal_string_append(sql, v) click to toggle source

Assume that SQL standard quoting is on, per Sequel's defaults

# File lib/sequel/adapters/shared/postgres.rb, line 1373
def literal_string_append(sql, v)
  sql << APOS << v.gsub(APOS_RE, DOUBLE_APOS) << APOS
end
literal_true() click to toggle source

PostgreSQL uses FALSE for false values

# File lib/sequel/adapters/shared/postgres.rb, line 1378
def literal_true
  BOOL_TRUE
end
select_clause_methods() click to toggle source

The order of clauses in the SELECT SQL statement

# File lib/sequel/adapters/shared/postgres.rb, line 1383
def select_clause_methods
  server_version >= 80400 ? SELECT_CLAUSE_METHODS_84 : SELECT_CLAUSE_METHODS
end
select_lock_sql(sql) click to toggle source

Support FOR SHARE locking when using the :share lock style.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1396
def select_lock_sql(sql)
  @opts[:lock] == :share ? (sql << FOR_SHARE) : super
end
select_window_sql(sql) click to toggle source

SQL fragment for named window specifications

# File lib/sequel/adapters/shared/postgres.rb, line 1401
def select_window_sql(sql)
  if ws = @opts[:window]
    sql << WINDOW
    c = false
    co = COMMA
    as = AS
    ws.map do |name, window|
      sql << co if c
      literal_append(sql, name)
      sql << as
      literal_append(sql, window)
      c ||= true
    end
  end
end
select_with_sql_base() click to toggle source

Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1418
def select_with_sql_base
  opts[:with].any?{|w| w[:recursive]} ? SQL_WITH_RECURSIVE : super
end
server_version() click to toggle source

The version of the database server

# File lib/sequel/adapters/shared/postgres.rb, line 1423
def server_version
  db.server_version(@opts[:server])
end
update_clause_methods() click to toggle source

PostgreSQL splits the main table from the joined tables

# File lib/sequel/adapters/shared/postgres.rb, line 1436
def update_clause_methods
  if server_version >= 90100
    UPDATE_CLAUSE_METHODS_91
  else
    UPDATE_CLAUSE_METHODS
  end
end
update_from_sql(sql) click to toggle source

Use FROM to specify additional tables in an update query

# File lib/sequel/adapters/shared/postgres.rb, line 1445
def update_from_sql(sql)
  join_from_sql(:FROM, sql)
end
update_table_sql(sql) click to toggle source

Only include the primary table in the main update clause

# File lib/sequel/adapters/shared/postgres.rb, line 1450
def update_table_sql(sql)
  sql << SPACE
  source_list_append(sql, @opts[:from][0..0])
end