module Sequel::Postgres::DatasetMethods

Constants

LOCK_MODES
NULL

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 1204
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 1211
def complex_expression_sql_append(sql, op, args)
  case op
  when :^
    j = ' # '
    c = false
    args.each do |a|
      sql << j if c
      literal_append(sql, a)
      c ||= true
    end
  when :ILIKE, :'NOT ILIKE'
    sql << '('
    literal_append(sql, args[0])
    sql << ' ' << op.to_s << ' '
    literal_append(sql, args[1])
    sql << " ESCAPE "
    literal_append(sql, "\\")
    sql << ')'
  else
    super
  end
end
disable_insert_returning() click to toggle source

Disables automatic use of INSERT … RETURNING. You can still use returning manually to force the use of RETURNING when inserting.

This is designed for cases where INSERT RETURNING cannot be used, such as when you are using partitioning with trigger functions or conditional rules, or when you are using a PostgreSQL version less than 8.2, or a PostgreSQL derivative that does not support returning.

Note that when this method is used, insert will not return the primary key of the inserted row, you will have to get the primary key of the inserted row before inserting via nextval, or after inserting via currval or lastval (making sure to use the same database connection for currval or lastval).

# File lib/sequel/adapters/shared/postgres.rb, line 1248
def disable_insert_returning
  clone(:disable_insert_returning=>true)
end
explain(opts=OPTS) click to toggle source

Return the results of an EXPLAIN query as a string

# File lib/sequel/adapters/shared/postgres.rb, line 1253
def explain(opts=OPTS)
  with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
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 1258
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 1321
def insert(*values)
  if @opts[:returning]
    # Already know which columns to return, let the standard code handle it
    super
  elsif @opts[:sql] || @opts[:disable_insert_returning]
    # Raw SQL used or RETURNING disabled, just use the default behavior
    # and return nil since sequence is not known.
    super
    nil
  else
    # Force the use of RETURNING with the primary key value,
    # unless it has been disabled.
    returning(insert_pk).insert(*values){|r| return r.values.first}
  end
end
insert_conflict(opts=OPTS) click to toggle source

Handle uniqueness violations when inserting, by updating the conflicting row, using ON CONFLICT. With no options, uses ON CONFLICT DO NOTHING. Options:

:conflict_where

The index filter, when using a partial index to determine uniqueness.

:constraint

An explicit constraint name, has precendence over :target.

:target

The column name or expression to handle uniqueness violations on.

:update

A hash of columns and values to set. Uses ON CONFLICT DO UPDATE.

:update_where

A WHERE condition to use for the update.

Examples:

DB[:table].insert_conflict.insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT DO NOTHING

DB[:table].insert_conflict(constraint: :table_a_uidx).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT ON CONSTRAINT table_a_uidx DO NOTHING

DB[:table].insert_conflict(target: :a).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO NOTHING

DB[:table].insert_conflict(target: :a, conflict_where: {c: true}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) WHERE (c IS TRUE) DO NOTHING

DB[:table].insert_conflict(target: :a, update: {b: Sequel[:excluded][:b]}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO UPDATE SET b = excluded.b

DB[:table].insert_conflict(constraint: :table_a_uidx,
  update: {b: Sequel[:excluded][:b]}, update_where: {Sequel[:table][:status_id] => 1}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT ON CONSTRAINT table_a_uidx
# DO UPDATE SET b = excluded.b WHERE (table.status_id = 1)
# File lib/sequel/adapters/shared/postgres.rb, line 1372
def insert_conflict(opts=OPTS)
  clone(:insert_conflict => opts)
end
insert_ignore() click to toggle source

Ignore uniqueness/exclusion violations when inserting, using ON CONFLICT DO NOTHING. Exists mostly for compatibility to MySQL's insert_ignore. Example:

DB[:table].insert_ignore.insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT DO NOTHING
# File lib/sequel/adapters/shared/postgres.rb, line 1382
def insert_ignore
  insert_conflict
end
insert_select(*values) click to toggle source

Insert a record returning the record inserted. Always returns nil without inserting a query if #disable_insert_returning is used.

# File lib/sequel/adapters/shared/postgres.rb, line 1388
def insert_select(*values)
  return unless supports_insert_select?
  server?(:default).with_sql_first(insert_select_sql(*values))
end
insert_select_sql(*values) click to toggle source

The SQL to use for an #insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

# File lib/sequel/adapters/shared/postgres.rb, line 1395
def insert_select_sql(*values)
  ds = opts[:returning] ? self : returning
  ds.insert_sql(*values)
end
lock(mode, opts=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 1405
def lock(mode, opts=OPTS)
  if block_given? # perform locking inside a transaction and yield to block
    @db.transaction(opts){lock(mode, opts); yield}
  else
    sql = 'LOCK TABLE '.dup
    source_list_append(sql, @opts[:from])
    mode = mode.to_s.upcase.strip
    unless LOCK_MODES.include?(mode)
      raise Error, "Unsupported lock mode: #{mode}"
    end
    sql << " IN #{mode} MODE"
    @db.execute(sql, opts)
  end
  nil
end
supports_cte?(type=:select) click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 1421
def supports_cte?(type=:select)
  if type == :select
    server_version >= 80400
  else
    server_version >= 90100
  end
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 1431
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 1436
def supports_distinct_on?
  true
end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

# File lib/sequel/adapters/shared/postgres.rb, line 1441
def supports_group_cube?
  server_version >= 90500
end
supports_group_rollup?() click to toggle source

PostgreSQL 9.5+ supports GROUP ROLLUP

# File lib/sequel/adapters/shared/postgres.rb, line 1446
def supports_group_rollup?
  server_version >= 90500
end
supports_grouping_sets?() click to toggle source

PostgreSQL 9.5+ supports GROUPING SETS

# File lib/sequel/adapters/shared/postgres.rb, line 1451
def supports_grouping_sets?
  server_version >= 90500
end
supports_insert_conflict?() click to toggle source

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

# File lib/sequel/adapters/shared/postgres.rb, line 1461
def supports_insert_conflict?
  server_version >= 90500
end
supports_insert_select?() click to toggle source

True unless insert returning has been disabled for this dataset.

# File lib/sequel/adapters/shared/postgres.rb, line 1456
def supports_insert_select?
  !@opts[:disable_insert_returning]
end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

# File lib/sequel/adapters/shared/postgres.rb, line 1466
def supports_lateral_subqueries?
  server_version >= 90300
end
supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

# File lib/sequel/adapters/shared/postgres.rb, line 1471
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 1481
def supports_regexp?
  true
end
supports_returning?(type) click to toggle source

Returning is always supported.

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

PostgreSQL 9.5+ supports SKIP LOCKED.

# File lib/sequel/adapters/shared/postgres.rb, line 1486
def supports_skip_locked?
  server_version >= 90500
end
supports_timestamp_timezones?() click to toggle source

PostgreSQL supports timezones in literal timestamps

# File lib/sequel/adapters/shared/postgres.rb, line 1491
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 1496
def supports_window_functions?
  server_version >= 80400
end
truncate(opts = 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"

DB[:table].truncate(cascade: true, only: true, restart: true)
# TRUNCATE TABLE ONLY "table" RESTART IDENTITY CASCADE
Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1516
def truncate(opts = 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. See {SQL::Window} for a list of options that can be passed in.

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

Protected Instance Methods

_import(columns, values, opts=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 1537
def _import(columns, values, opts=OPTS)
  if @opts[:returning]
    statements = multi_insert_sql(columns, values)
    @db.transaction(Hash[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 1553
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 1559
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 1674
def compound_dataset_sql_append(sql, ds)
  sql << '('
  super
  sql << ')'
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 1565
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 1571
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 1741
def full_text_string_join(cols)
  cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
  cols = cols.zip([' '] * cols.length).flatten
  cols.pop
  SQL::StringExpression.new(:'||', *cols)
end
insert_conflict_sql(sql) click to toggle source

Add ON CONFLICT clause if it should be used

# File lib/sequel/adapters/shared/postgres.rb, line 1576
def insert_conflict_sql(sql)
  if opts = @opts[:insert_conflict]
    sql << " ON CONFLICT"

    if target = opts[:constraint] 
      sql << " ON CONSTRAINT "
      identifier_append(sql, target)
    elsif target = opts[:target]
      sql << ' '
      identifier_append(sql, Array(target))
      if conflict_where = opts[:conflict_where]
        sql << " WHERE "
        literal_append(sql, conflict_where)
      end
    end

    if values = opts[:update]
      sql << " DO UPDATE SET "
      update_sql_values_hash(sql, values)
      if update_where = opts[:update_where]
        sql << " WHERE "
        literal_append(sql, update_where)
      end
    else
      sql << " DO NOTHING"
    end
  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 1606
def insert_pk
  if (f = opts[:from]) && !f.empty?
    case t = f.first
    when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
      if pk = db.primary_key(t)
        Sequel::SQL::Identifier.new(pk)
      end
    end
  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 1619
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 << ' ' << type.to_s << ' '
    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 1630
def literal_blob_append(sql, v)
  sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

# File lib/sequel/adapters/shared/postgres.rb, line 1635
def literal_false
  '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 1640
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 1653
def literal_string_append(sql, v)
  sql << "'" << v.gsub("'", "''") << "'"
end
literal_true() click to toggle source

PostgreSQL uses true for true values

# File lib/sequel/adapters/shared/postgres.rb, line 1658
def literal_true
  'true'
end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

# File lib/sequel/adapters/shared/postgres.rb, line 1663
def multi_insert_sql_strategy
  :values
end
non_sql_option?(key) click to toggle source

Dataset options that do not affect the generated SQL.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1668
def non_sql_option?(key)
  super || key == :cursor || key == :insert_conflict
end
select_lock_sql(sql) click to toggle source

Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1682
def select_lock_sql(sql)
  if @opts[:lock] == :share
    sql << ' FOR SHARE'
  else
    super
  end

  if @opts[:skip_locked]
    sql << " SKIP LOCKED"
  end
end
select_values_sql(sql) click to toggle source

Support VALUES clause instead of the SELECT clause to return rows.

# File lib/sequel/adapters/shared/postgres.rb, line 1695
def select_values_sql(sql)
  sql << "VALUES "
  expression_list_append(sql, opts[:values])
end
select_window_sql(sql) click to toggle source

SQL fragment for named window specifications

# File lib/sequel/adapters/shared/postgres.rb, line 1701
def select_window_sql(sql)
  if ws = @opts[:window]
    sql << " WINDOW "
    c = false
    co = ', '
    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 1718
def select_with_sql_base
  opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
end
server_version() click to toggle source

The version of the database server

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

PostgreSQL supports quoted function names.

# File lib/sequel/adapters/shared/postgres.rb, line 1728
def supports_quoted_function_names?
  true
end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1732
def to_prepared_statement(type, *a)
  if type == :insert && !@opts.has_key?(:returning)
    returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
  else
    super
  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 1749
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 1754
def update_table_sql(sql)
  sql << ' '
  source_list_append(sql, @opts[:from][0..0])
end