module Sequel::Postgres::DatabaseMethods

Constants

DATABASE_ERROR_REGEXPS
FOREIGN_KEY_LIST_ON_DELETE_MAP
MAX_DATE
MAX_TIMESTAMP
MIN_DATE
MIN_TIMESTAMP
ON_COMMIT
SELECT_CUSTOM_SEQUENCE_SQL

SQL fragment for custom sequences (ones not created by serial primary key), Returning the schema and literal form of the sequence name, by parsing the column defaults table.

SELECT_PK_SQL

SQL fragment for determining primary key column for the given table. Only returns the first primary key if the table has a composite primary key.

SELECT_SERIAL_SEQUENCE_SQL

SQL fragment for getting sequence associated with table's primary key, assuming it was a serial primary key column.

TYPTYPE_METHOD_MAP
VALID_CLIENT_MIN_MESSAGES

Attributes

conversion_procs[R]

A hash of conversion procs, keyed by type integer (oid) and having callable values for the conversion proc for that type.

Public Instance Methods

add_conversion_proc(oid, callable=nil, &block) click to toggle source

Set a conversion proc for the given oid. The callable can be passed either as a argument or a block.

# File lib/sequel/adapters/shared/postgres.rb, line 308
def add_conversion_proc(oid, callable=nil, &block)
  conversion_procs[oid] = callable || block
end
add_named_conversion_proc(name, &block) click to toggle source

Add a conversion proc for a named type, using the given block. This should be used for types without fixed OIDs, which includes all types that are not included in a default PostgreSQL installation.

# File lib/sequel/adapters/shared/postgres.rb, line 315
def add_named_conversion_proc(name, &block)
  unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
    raise Error, "No matching type in pg_type for #{name.inspect}"
  end
  add_conversion_proc(oid, block)
end
check_constraints(table) click to toggle source

A hash of metadata for CHECK constraints on the table. Keys are CHECK constraint name symbols. Values are hashes with the following keys:

:definition

An SQL fragment for the definition of the constraint

:columns

An array of column symbols for the columns referenced in the constraint, can be an empty array if the database cannot deteremine the column symbols.

# File lib/sequel/adapters/shared/postgres.rb, line 331
def check_constraints(table)
  m = output_identifier_meth

  hash = {}
  _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row|
    constraint = m.call(row[:constraint])
    entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]}
    entry[:columns] << m.call(row[:column]) if row[:column]
  end
  
  hash
end
commit_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 322
def commit_prepared_transaction(transaction_id, opts=OPTS)
  run("COMMIT PREPARED #{literal(transaction_id)}", opts)
end
convert_serial_to_identity(table, opts=OPTS) click to toggle source

Convert the first primary key column in the table from being a serial column to being an identity column. If the column is already an identity column, assume it was already converted and make no changes.

Only supported on PostgreSQL 10.2+, since on those versions Sequel will use identity columns instead of serial columns for auto incrementing primary keys. Only supported when running as a superuser, since regular users cannot modify system tables, and there is no way to keep an existing sequence when changing an existing column to be an identity column.

This method can raise an exception in at least the following cases where it may otherwise succeed (there may be additional cases not listed here):

  • The serial column was added after table creation using PostgreSQL <7.3

  • A regular index also exists on the column (such an index can probably be dropped as the primary key index should suffice)

Options:

:column

Specify the column to convert instead of using the first primary key column

:server

Run the SQL on the given server

# File lib/sequel/adapters/shared/postgres.rb, line 362
def convert_serial_to_identity(table, opts=OPTS)
  raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002

  server = opts[:server]
  server_hash = server ? {:server=>server} : OPTS
  ds = dataset
  ds = ds.server(server) if server

  raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'

  table_oid = regclass_oid(table)
  im = input_identifier_meth
  unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
    raise Error, "could not determine column to convert from serial to identity automatically"
  end
  column = im.call(column)

  column_num = ds.from(:pg_attribute).
    where(:attrelid=>table_oid, :attname=>column).
    get(:attnum)

  pg_class = Sequel.cast('pg_class', :regclass)
  res = ds.from(:pg_depend).
    where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
    select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])

  case res.length
  when 0
    raise Error, "unable to find related sequence when converting serial to identity"
  when 1
    seq_oid, already_identity = res.first
  else
    raise Error, "more than one linked sequence found when converting serial to identity"
  end

  return if already_identity

  transaction(server_hash) do
    run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash)

    ds.from(:pg_depend).
      where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
      update(:deptype=>'i')

    ds.from(:pg_attribute).
      where(:attrelid=>table_oid, :attname=>column).
      update(:attidentity=>'d')
  end

  remove_cached_schema(table)
  nil
end
create_function(name, definition, opts=OPTS) click to toggle source

Creates the function in the database. Arguments:

name

name of the function to create

definition

string definition of the function, or object file for a dynamically loaded C function.

opts

options hash:

:args

function arguments, can be either a symbol or string specifying a type or an array of 1-3 elements:

1

argument data type

2

argument name

3

argument mode (e.g. in, out, inout)

:behavior

Should be IMMUTABLE, STABLE, or VOLATILE. PostgreSQL assumes VOLATILE by default.

:parallel

The thread safety attribute of the function. Should be SAFE, UNSAFE, RESTRICTED. PostgreSQL assumes UNSAFE by default.

:cost

The estimated cost of the function, used by the query planner.

:language

The language the function uses. SQL is the default.

:link_symbol

For a dynamically loaded see function, the function's link symbol if different from the definition argument.

:returns

The data type returned by the function. If you are using OUT or INOUT argument modes, this is ignored. Otherwise, if this is not specified, void is used by default to specify the function is not supposed to return a value.

:rows

The estimated number of rows the function will return. Only use if the function returns SETOF something.

:security_definer

Makes the privileges of the function the same as the privileges of the user who defined the function instead of the privileges of the user who runs the function. There are security implications when doing this, see the PostgreSQL documentation.

:set

Configuration variables to set while the function is being run, can be a hash or an array of two pairs. search_path is often used here if :security_definer is used.

:strict

Makes the function return NULL when any argument is NULL.

# File lib/sequel/adapters/shared/postgres.rb, line 436
def create_function(name, definition, opts=OPTS)
  self << create_function_sql(name, definition, opts)
end
create_language(name, opts=OPTS) click to toggle source

Create the procedural language in the database. Arguments:

name

Name of the procedural language (e.g. plpgsql)

opts

options hash:

:handler

The name of a previously registered function used as a call handler for this language.

:replace

Replace the installed language if it already exists (on PostgreSQL 9.0+).

:trusted

Marks the language being created as trusted, allowing unprivileged users to create functions using this language.

:validator

The name of previously registered function used as a validator of functions defined in this language.

# File lib/sequel/adapters/shared/postgres.rb, line 447
def create_language(name, opts=OPTS)
  self << create_language_sql(name, opts)
end
create_schema(name, opts=OPTS) click to toggle source

Create a schema in the database. Arguments:

name

Name of the schema (e.g. admin)

opts

options hash:

:if_not_exists

Don't raise an error if the schema already exists (PostgreSQL 9.3+)

:owner

The owner to set for the schema (defaults to current user if not specified)

# File lib/sequel/adapters/shared/postgres.rb, line 456
def create_schema(name, opts=OPTS)
  self << create_schema_sql(name, opts)
end
create_table(name, options=OPTS, &block) click to toggle source

Support partitions of tables using the :partition_of option.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 461
def create_table(name, options=OPTS, &block)
  if options[:partition_of]
    create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
    return
  end

  super
end
create_table?(name, options=OPTS, &block) click to toggle source

Support partitions of tables using the :partition_of option.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 471
def create_table?(name, options=OPTS, &block)
  if options[:partition_of]
    create_table(name, options.merge!(:if_not_exists=>true), &block)
    return
  end

  super
end
create_trigger(table, name, function, opts=OPTS) click to toggle source

Create a trigger in the database. Arguments:

table

the table on which this trigger operates

name

the name of this trigger

function

the function to call for this trigger, which should return type trigger.

opts

options hash:

:after

Calls the trigger after execution instead of before.

:args

An argument or array of arguments to pass to the function.

:each_row

Calls the trigger for each row instead of for each statement.

:events

Can be :insert, :update, :delete, or an array of any of those. Calls the trigger whenever that type of statement is used. By default, the trigger is called for insert, update, or delete.

:replace

Replace the trigger with the same name if it already exists (PostgreSQL 14+).

:when

A filter to use for the trigger

# File lib/sequel/adapters/shared/postgres.rb, line 492
def create_trigger(table, name, function, opts=OPTS)
  self << create_trigger_sql(table, name, function, opts)
end
database_type() click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 496
def database_type
  :postgres
end
defer_constraints(opts=OPTS) click to toggle source

For constraints that are deferrable, defer constraints until transaction commit. Options:

:constraints

An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.

:server

The server/shard on which to run the query.

Examples:

DB.defer_constraints
# SET CONSTRAINTS ALL DEFERRED

DB.defer_constraints(constraints: [:c1, Sequel[:sc][:c2]])
# SET CONSTRAINTS "c1", "sc"."s2" DEFERRED
# File lib/sequel/adapters/shared/postgres.rb, line 515
def defer_constraints(opts=OPTS)
  _set_constraints(' DEFERRED', opts)
end
do(code, opts=OPTS) click to toggle source

Use PostgreSQL's DO syntax to execute an anonymous code block. The code should be the literal code string to use in the underlying procedural language. Options:

:language

The procedural language the code is written in. The PostgreSQL default is plpgsql. Can be specified as a string or a symbol.

# File lib/sequel/adapters/shared/postgres.rb, line 524
def do(code, opts=OPTS)
  language = opts[:language]
  run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}"
end
drop_function(name, opts=OPTS) click to toggle source

Drops the function from the database. Arguments:

name

name of the function to drop

opts

options hash:

:args

The arguments for the function. See create_function_sql.

:cascade

Drop other objects depending on this function.

:if_exists

Don't raise an error if the function doesn't exist.

# File lib/sequel/adapters/shared/postgres.rb, line 535
def drop_function(name, opts=OPTS)
  self << drop_function_sql(name, opts)
end
drop_language(name, opts=OPTS) click to toggle source

Drops a procedural language from the database. Arguments:

name

name of the procedural language to drop

opts

options hash:

:cascade

Drop other objects depending on this function.

:if_exists

Don't raise an error if the function doesn't exist.

# File lib/sequel/adapters/shared/postgres.rb, line 544
def drop_language(name, opts=OPTS)
  self << drop_language_sql(name, opts)
end
drop_schema(name, opts=OPTS) click to toggle source

Drops a schema from the database. Arguments:

name

name of the schema to drop

opts

options hash:

:cascade

Drop all objects in this schema.

:if_exists

Don't raise an error if the schema doesn't exist.

# File lib/sequel/adapters/shared/postgres.rb, line 553
def drop_schema(name, opts=OPTS)
  self << drop_schema_sql(name, opts)
end
drop_trigger(table, name, opts=OPTS) click to toggle source

Drops a trigger from the database. Arguments:

table

table from which to drop the trigger

name

name of the trigger to drop

opts

options hash:

:cascade

Drop other objects depending on this function.

:if_exists

Don't raise an error if the function doesn't exist.

# File lib/sequel/adapters/shared/postgres.rb, line 563
def drop_trigger(table, name, opts=OPTS)
  self << drop_trigger_sql(table, name, opts)
end
foreign_key_list(table, opts=OPTS) click to toggle source

Return full foreign key information using the pg system tables, including :name, :on_delete, :on_update, and :deferrable entries in the hashes.

Supports additional options:

:reverse

Instead of returning foreign keys in the current table, return foreign keys in other tables that reference the current table.

:schema

Set to true to have the :table value in the hashes be a qualified identifier. Set to false to use a separate :schema value with the related schema. Defaults to whether the given table argument is a qualified identifier.

# File lib/sequel/adapters/shared/postgres.rb, line 577
def foreign_key_list(table, opts=OPTS)
  m = output_identifier_meth
  schema, _ = opts.fetch(:schema, schema_and_table(table))

  h = {}
  fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
  reverse = opts[:reverse]

  (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row|
    if reverse
      key = [row[:schema], row[:table], row[:name]]
    else
      key = row[:name]
    end

    if r = h[key]
      r[:columns] << m.call(row[:column])
      r[:key] << m.call(row[:refcolumn])
    else
      entry = h[key] = {
        :name=>m.call(row[:name]),
        :columns=>[m.call(row[:column])],
        :key=>[m.call(row[:refcolumn])],
        :on_update=>fklod_map[row[:on_update]],
        :on_delete=>fklod_map[row[:on_delete]],
        :deferrable=>row[:deferrable],
        :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
      }

      unless schema
        # If not combining schema information into the :table entry
        # include it as a separate entry.
        entry[:schema] = m.call(row[:schema])
      end
    end
  end

  h.values
end
freeze() click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 617
def freeze
  server_version
  supports_prepared_transactions?
  _schema_ds
  _select_serial_sequence_ds
  _select_custom_sequence_ds
  _select_pk_ds
  _indexes_ds
  _check_constraints_ds
  _foreign_key_list_ds
  _reverse_foreign_key_list_ds
  @conversion_procs.freeze
  super
end
immediate_constraints(opts=OPTS) click to toggle source

Immediately apply deferrable constraints.

:constraints

An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.

:server

The server/shard on which to run the query.

Examples:

DB.immediate_constraints
# SET CONSTRAINTS ALL IMMEDIATE

DB.immediate_constraints(constraints: [:c1, Sequel[:sc][:c2]])
# SET CONSTRAINTS "c1", "sc"."s2" IMMEDIATE
# File lib/sequel/adapters/shared/postgres.rb, line 646
def immediate_constraints(opts=OPTS)
  _set_constraints(' IMMEDIATE', opts)
end
indexes(table, opts=OPTS) click to toggle source

Use the pg_* system tables to determine indexes on a table

# File lib/sequel/adapters/shared/postgres.rb, line 651
def indexes(table, opts=OPTS)
  m = output_identifier_meth
  cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)}
  cond[:indpred] = nil unless opts[:include_partial]

  indexes = {}
  _indexes_ds.where_each(cond) do |r|
    i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
    i[:columns] << m.call(r[:column])
  end
  indexes
end
locks() click to toggle source

Dataset containing all current database locks

# File lib/sequel/adapters/shared/postgres.rb, line 665
def locks
  dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
end
notify(channel, opts=OPTS) click to toggle source

Notifies the given channel. See the PostgreSQL NOTIFY documentation. Options:

:payload

The payload string to use for the NOTIFY statement. Only supported in PostgreSQL 9.0+.

:server

The server to which to send the NOTIFY statement, if the sharding support is being used.

# File lib/sequel/adapters/shared/postgres.rb, line 675
def notify(channel, opts=OPTS)
  sql = String.new
  sql << "NOTIFY "
  dataset.send(:identifier_append, sql, channel)
  if payload = opts[:payload]
    sql << ", "
    dataset.literal_append(sql, payload.to_s)
  end
  execute_ddl(sql, opts)
end
primary_key(table, opts=OPTS) click to toggle source

Return primary key for the given table.

# File lib/sequel/adapters/shared/postgres.rb, line 687
def primary_key(table, opts=OPTS)
  quoted_table = quote_schema_table(table)
  Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
  value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts))
  Sequel.synchronize{@primary_keys[quoted_table] = value}
end
primary_key_sequence(table, opts=OPTS) click to toggle source

Return the sequence providing the default for the primary key for the given table.

# File lib/sequel/adapters/shared/postgres.rb, line 695
def primary_key_sequence(table, opts=OPTS)
  quoted_table = quote_schema_table(table)
  Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
  cond = {Sequel[:t][:oid] => regclass_oid(table, opts)}
  value = if pks = _select_serial_sequence_ds.first(cond)
    literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
  elsif pks = _select_custom_sequence_ds.first(cond)
    literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
  end

  Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value
end
refresh_view(name, opts=OPTS) click to toggle source

Refresh the materialized view with the given name.

DB.refresh_view(:items_view)
# REFRESH MATERIALIZED VIEW items_view
DB.refresh_view(:items_view, concurrently: true)
# REFRESH MATERIALIZED VIEW CONCURRENTLY items_view
# File lib/sequel/adapters/shared/postgres.rb, line 714
def refresh_view(name, opts=OPTS)
  run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
end
reset_primary_key_sequence(table) click to toggle source

Reset the primary key sequence for the given table, basing it on the maximum current value of the table's primary key.

# File lib/sequel/adapters/shared/postgres.rb, line 720
def reset_primary_key_sequence(table)
  return unless seq = primary_key_sequence(table)
  pk = SQL::Identifier.new(primary_key(table))
  db = self
  s, t = schema_and_table(table)
  table = Sequel.qualify(s, t) if s

  if server_version >= 100000
    seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq)))
    increment_by = :seqincrement
    min_value = :seqmin
  # :nocov:
  else
    seq_ds = metadata_dataset.from(LiteralString.new(seq))
    increment_by = :increment_by
    min_value = :min_value
  # :nocov:
  end

  get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
end
rollback_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 742
def rollback_prepared_transaction(transaction_id, opts=OPTS)
  run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
end
serial_primary_key_options() click to toggle source

PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.

# File lib/sequel/adapters/shared/postgres.rb, line 748
def serial_primary_key_options
  # :nocov:
  auto_increment_key = server_version >= 100002 ? :identity : :serial
  # :nocov:
  {:primary_key => true, auto_increment_key => true, :type=>Integer}
end
server_version(server=nil) click to toggle source

The version of the PostgreSQL server, used for determining capability.

# File lib/sequel/adapters/shared/postgres.rb, line 756
def server_version(server=nil)
  return @server_version if @server_version
  ds = dataset
  ds = ds.server(server) if server
  @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
end
supports_create_table_if_not_exists?() click to toggle source

PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+

# File lib/sequel/adapters/shared/postgres.rb, line 764
def supports_create_table_if_not_exists?
  server_version >= 90100
end
supports_deferrable_constraints?() click to toggle source

PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.

# File lib/sequel/adapters/shared/postgres.rb, line 769
def supports_deferrable_constraints?
  server_version >= 90000
end
supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

# File lib/sequel/adapters/shared/postgres.rb, line 774
def supports_deferrable_foreign_key_constraints?
  true
end
supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

# File lib/sequel/adapters/shared/postgres.rb, line 779
def supports_drop_table_if_exists?
  true
end
supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

# File lib/sequel/adapters/shared/postgres.rb, line 784
def supports_partial_indexes?
  true
end
supports_prepared_transactions?() click to toggle source

PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.

# File lib/sequel/adapters/shared/postgres.rb, line 795
def supports_prepared_transactions?
  return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
  @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
end
supports_savepoints?() click to toggle source

PostgreSQL supports savepoints

# File lib/sequel/adapters/shared/postgres.rb, line 801
def supports_savepoints?
  true
end
supports_transaction_isolation_levels?() click to toggle source

PostgreSQL supports transaction isolation levels

# File lib/sequel/adapters/shared/postgres.rb, line 806
def supports_transaction_isolation_levels?
  true
end
supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

# File lib/sequel/adapters/shared/postgres.rb, line 811
def supports_transactional_ddl?
  true
end
supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

# File lib/sequel/adapters/shared/postgres.rb, line 789
def supports_trigger_conditions?
  server_version >= 90000
end
tables(opts=OPTS, &block) click to toggle source

Array of symbols specifying table names in the current database. The dataset used is yielded to the block if one is provided, otherwise, an array of symbols of table names is returned.

Options:

:qualify

Return the tables as Sequel::SQL::QualifiedIdentifier instances, using the schema the table is located in as the qualifier.

:schema

The schema to search

:server

The server to use

# File lib/sequel/adapters/shared/postgres.rb, line 824
def tables(opts=OPTS, &block)
  pg_class_relname(['r', 'p'], opts, &block)
end
type_supported?(type) click to toggle source

Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.

# File lib/sequel/adapters/shared/postgres.rb, line 830
def type_supported?(type)
  Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
  supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
  Sequel.synchronize{return @supported_types[type] = supported}
end
values(v) click to toggle source

Creates a dataset that uses the VALUES clause:

DB.values([[1, 2], [3, 4]])
# VALUES ((1, 2), (3, 4))

DB.values([[1, 2], [3, 4]]).order(:column2).limit(1, 1)
# VALUES ((1, 2), (3, 4)) ORDER BY column2 LIMIT 1 OFFSET 1
# File lib/sequel/adapters/shared/postgres.rb, line 843
def values(v)
  raise Error, "Cannot provide an empty array for values" if v.empty?
  @default_dataset.clone(:values=>v)
end
views(opts=OPTS) click to toggle source

Array of symbols specifying view names in the current database.

Options:

:materialized

Return materialized views

:qualify

Return the views as Sequel::SQL::QualifiedIdentifier instances, using the schema the view is located in as the qualifier.

:schema

The schema to search

:server

The server to use

# File lib/sequel/adapters/shared/postgres.rb, line 856
def views(opts=OPTS)
  relkind = opts[:materialized] ? 'm' : 'v'
  pg_class_relname(relkind, opts)
end
with_advisory_lock(lock_id, opts=OPTS) { || ... } click to toggle source

Attempt to acquire an exclusive advisory lock with the given lock_id (which should be a 64-bit integer). If successful, yield to the block, then release the advisory lock when the block exits. If unsuccessful, raise a Sequel::AdvisoryLockError.

DB.with_advisory_lock(1347){DB.get(1)}
# SELECT pg_try_advisory_lock(1357) LIMIT 1
# SELECT 1 AS v LIMIT 1
# SELECT pg_advisory_unlock(1357) LIMIT 1

Options:

:wait

Do not raise an error, instead, wait until the advisory lock can be acquired.

# File lib/sequel/adapters/shared/postgres.rb, line 872
def with_advisory_lock(lock_id, opts=OPTS)
  ds = dataset
  if server = opts[:server]
    ds = ds.server(server)
  end

  synchronize(server) do |c|
    begin
      if opts[:wait]
        ds.get{pg_advisory_lock(lock_id)}
        locked = true
      else
        unless locked = ds.get{pg_try_advisory_lock(lock_id)}
          raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}"
        end
      end

      yield
    ensure
      ds.get{pg_advisory_unlock(lock_id)} if locked
    end
  end
end

Private Instance Methods

__foreign_key_list_ds(reverse) click to toggle source

Build dataset used for foreign key list methods.

# File lib/sequel/adapters/shared/postgres.rb, line 918
def __foreign_key_list_ds(reverse)
  if reverse
    ctable = Sequel[:att2]
    cclass = Sequel[:cl2]
    rtable = Sequel[:att]
    rclass = Sequel[:cl]
  else
    ctable = Sequel[:att]
    cclass = Sequel[:cl]
    rtable = Sequel[:att2]
    rclass = Sequel[:cl2]
  end

  if server_version >= 90500
    cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
    rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
  # :nocov:
  else
    range = 0...32
    cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
    rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
  # :nocov:
  end

  ds = metadata_dataset.
    from{pg_constraint.as(:co)}.
    join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
    join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
    join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
    join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
    join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
    order{[co[:conname], cpos]}.
    where{{
      cl[:relkind]=>%w'r p',
      co[:contype]=>'f',
      cpos=>rpos
    }}.
    select{[
      co[:conname].as(:name),
      ctable[:attname].as(:column),
      co[:confupdtype].as(:on_update),
      co[:confdeltype].as(:on_delete),
      cl2[:relname].as(:table),
      rtable[:attname].as(:refcolumn),
      SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
      nsp[:nspname].as(:schema)
    ]}

  if reverse
    ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
  end

  ds
end
_check_constraints_ds() click to toggle source

Dataset used to retrieve CHECK constraint information

# File lib/sequel/adapters/shared/postgres.rb, line 899
def _check_constraints_ds
  @_check_constraints_ds ||= metadata_dataset.
    from{pg_constraint.as(:co)}.
    left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
    where(:contype=>'c').
    select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
end
_foreign_key_list_ds() click to toggle source

Dataset used to retrieve foreign keys referenced by a table

# File lib/sequel/adapters/shared/postgres.rb, line 908
def _foreign_key_list_ds
  @_foreign_key_list_ds ||= __foreign_key_list_ds(false)
end
_indexes_ds() click to toggle source

Dataset used to retrieve index information

# File lib/sequel/adapters/shared/postgres.rb, line 974
def _indexes_ds
  @_indexes_ds ||= begin
    if server_version >= 90500
      order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
    # :nocov:
    else
      range = 0...32
      order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
    # :nocov:
    end

    attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey])

    ds = metadata_dataset.
      from{pg_class.as(:tab)}.
      join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
      join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
      join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
      left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
      where{{
        indc[:relkind]=>%w'i I',
        ind[:indisprimary]=>false,
        :indexprs=>nil,
        :indisvalid=>true}}.
      order(*order).
      select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}

    # :nocov:
    ds = ds.where(:indisready=>true) if server_version >= 80300
    ds = ds.where(:indislive=>true) if server_version >= 90300
    # :nocov:

    ds
  end
end
_reverse_foreign_key_list_ds() click to toggle source

Dataset used to retrieve foreign keys referencing a table

# File lib/sequel/adapters/shared/postgres.rb, line 913
def _reverse_foreign_key_list_ds
  @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true)
end
_schema_ds() click to toggle source

Dataset used to get schema for tables

# File lib/sequel/adapters/shared/postgres.rb, line 1071
def _schema_ds
  @_schema_ds ||= begin
    ds = metadata_dataset.select{[
        pg_attribute[:attname].as(:name),
        SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid),
        SQL::Cast.new(basetype[:oid], :integer).as(:base_oid),
        SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type),
        SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type),
        SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default),
        SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null),
        SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key),
        Sequel[:pg_type][:typtype],
        (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array),
      ]}.
      from(:pg_class).
      join(:pg_attribute, :attrelid=>:oid).
      join(:pg_type, :oid=>:atttypid).
      left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype).
      left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]).
      left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]).
      left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true).
      where{{pg_attribute[:attisdropped]=>false}}.
      where{pg_attribute[:attnum] > 0}.
      order{pg_attribute[:attnum]}

    # :nocov:
    if server_version > 100000
    # :nocov:
      ds = ds.select_append{pg_attribute[:attidentity]}

      # :nocov:
      if server_version > 120000
      # :nocov:
        ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)}
      end
    end

    ds
  end
end
_select_custom_sequence_ds() click to toggle source

Dataset used to determine custom serial sequences for tables

# File lib/sequel/adapters/shared/postgres.rb, line 1011
def _select_custom_sequence_ds
  @_select_custom_sequence_ds ||= metadata_dataset.
    from{pg_class.as(:t)}.
    join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name).
    join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr).
    join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def).
    join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons).
    where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}.
    select{
      expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2)
      [
        name[:nspname].as(:schema),
        Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence)
      ]
    }
end
_select_pk_ds() click to toggle source

Dataset used to determine primary keys for tables

# File lib/sequel/adapters/shared/postgres.rb, line 1057
def _select_pk_ds
  @_select_pk_ds ||= metadata_dataset.
    from(:pg_class, :pg_attribute, :pg_index, :pg_namespace).
    where{[
      [pg_class[:oid], pg_attribute[:attrelid]],
      [pg_class[:relnamespace], pg_namespace[:oid]],
      [pg_class[:oid], pg_index[:indrelid]],
      [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]],
      [pg_index[:indisprimary], 't']
    ]}.
    select{pg_attribute[:attname].as(:pk)}
end
_select_serial_sequence_ds() click to toggle source

Dataset used to determine normal serial sequences for tables

# File lib/sequel/adapters/shared/postgres.rb, line 1029
def _select_serial_sequence_ds
  @_serial_sequence_ds ||= metadata_dataset.
    from{[
      pg_class.as(:seq),
      pg_attribute.as(:attr),
      pg_depend.as(:dep),
      pg_namespace.as(:name),
      pg_constraint.as(:cons),
      pg_class.as(:t)
    ]}.
    where{[
      [seq[:oid], dep[:objid]],
      [seq[:relnamespace], name[:oid]],
      [seq[:relkind], 'S'],
      [attr[:attrelid], dep[:refobjid]],
      [attr[:attnum], dep[:refobjsubid]],
      [attr[:attrelid], cons[:conrelid]],
      [attr[:attnum], cons[:conkey].sql_subscript(1)],
      [attr[:attrelid], t[:oid]],
      [cons[:contype], 'p']
    ]}.
    select{[
      name[:nspname].as(:schema),
      seq[:relname].as(:sequence)
    ]}
end
_set_constraints(type, opts) click to toggle source

Internals of defer_constraints/immediate_constraints

# File lib/sequel/adapters/shared/postgres.rb, line 1113
def _set_constraints(type, opts)
  execute_ddl(_set_constraints_sql(type, opts), opts)
end
_set_constraints_sql(type, opts) click to toggle source

SQL to use for SET CONSTRAINTS

# File lib/sequel/adapters/shared/postgres.rb, line 1118
def _set_constraints_sql(type, opts)
  sql = String.new
  sql << "SET CONSTRAINTS "
  if constraints = opts[:constraints]
    dataset.send(:source_list_append, sql, Array(constraints))
  else
    sql << "ALL"
  end
  sql << type
end
_table_exists?(ds) click to toggle source

Consider lock or statement timeout errors as evidence that the table exists but is locked.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1131
def _table_exists?(ds)
  super
rescue DatabaseError => e    
  raise e unless /canceling statement due to (?:statement|lock) timeout/ =~ e.message 
end
alter_table_add_column_sql(table, op) click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 1137
def alter_table_add_column_sql(table, op)
  "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}"
end
alter_table_drop_column_sql(table, op) click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 1155
def alter_table_drop_column_sql(table, op)
  "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}"
end
alter_table_generator_class() click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 1141
def alter_table_generator_class
  Postgres::AlterTableGenerator
end
alter_table_set_column_type_sql(table, op) click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1145
def alter_table_set_column_type_sql(table, op)
  s = super
  if using = op[:using]
    using = Sequel::LiteralString.new(using) if using.is_a?(String)
    s += ' USING '
    s << literal(using)
  end
  s
end
alter_table_validate_constraint_sql(table, op) click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 1159
def alter_table_validate_constraint_sql(table, op)
  "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
end
begin_new_transaction(conn, opts) click to toggle source

If the :synchronous option is given and non-nil, set synchronous_commit appropriately. Valid values for the :synchronous option are true, :on, false, :off, :local, and :remote_write.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1166
def begin_new_transaction(conn, opts)
  super
  if opts.has_key?(:synchronous)
    case sync = opts[:synchronous]
    when true
      sync = :on
    when false
      sync = :off
    when nil
      return
    end

    log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
  end
end
begin_savepoint(conn, opts) click to toggle source

Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1183
def begin_savepoint(conn, opts)
  super

  unless (read_only = opts[:read_only]).nil?
    log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
  end
end
column_definition_collate_sql(sql, column) click to toggle source

Literalize non-String collate options. This is because unquoted collatations are folded to lowercase, and PostgreSQL used mixed case or capitalized collations.

# File lib/sequel/adapters/shared/postgres.rb, line 1193
def column_definition_collate_sql(sql, column)
  if collate = column[:collate]
    collate = literal(collate) unless collate.is_a?(String)
    sql << " COLLATE #{collate}"
  end
end
column_definition_default_sql(sql, column) click to toggle source

Support identity columns, but only use the identity SQL syntax if no default value is given.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1202
def column_definition_default_sql(sql, column)
  super
  if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
    if (identity = column[:identity])
      sql << " GENERATED "
      sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
      sql << " AS IDENTITY"
    elsif (generated = column[:generated_always_as])
      sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED"
    end
  end
end
column_schema_normalize_default(default, type) click to toggle source

Handle PostgreSQL specific default format.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1216
def column_schema_normalize_default(default, type)
  if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
    default = m[1] || m[2]
  end
  super(default, type)
end
combinable_alter_table_op?(op) click to toggle source

PostgreSQL can't combine rename_column operations, and it can combine the custom validate_constraint operation.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1235
def combinable_alter_table_op?(op)
  (super || op[:op] == :validate_constraint) && op[:op] != :rename_column
end
commit_transaction(conn, opts=OPTS) click to toggle source

If the :prepare option is given and we aren't in a savepoint, prepare the transaction for a two-phase commit.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1225
def commit_transaction(conn, opts=OPTS)
  if (s = opts[:prepare]) && savepoint_level(conn) <= 1
    log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
  else
    super
  end
end
connection_configuration_sqls(opts=@opts) click to toggle source

The SQL queries to execute when starting a new connection.

# File lib/sequel/adapters/shared/postgres.rb, line 1241
def connection_configuration_sqls(opts=@opts)
  sqls = []

  sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))

  cmm = opts.fetch(:client_min_messages, :warning)
  if cmm && !cmm.to_s.empty?
    cmm = cmm.to_s.upcase.strip
    unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
      raise Error, "Unsupported client_min_messages setting: #{cmm}"
    end
    sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
  end

  if search_path = opts[:search_path]
    case search_path
    when String
      search_path = search_path.split(",").map(&:strip)
    when Array
      # nil
    else
      raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
    end
    sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
  end

  sqls
end
constraint_definition_sql(constraint) click to toggle source

Handle exclusion constraints.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1271
def constraint_definition_sql(constraint)
  case constraint[:type]
  when :exclude
    elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
    sql = String.new
    sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}"
    constraint_deferrable_sql_append(sql, constraint[:deferrable])
    sql
  when :foreign_key, :check
    sql = super
    if constraint[:not_valid]
      sql << " NOT VALID"
    end
    sql
  else
    super
  end
end
copy_into_sql(table, opts) click to toggle source

SQL for doing fast table insert from stdin.

# File lib/sequel/adapters/shared/postgres.rb, line 1319
def copy_into_sql(table, opts)
  sql = String.new
  sql << "COPY #{literal(table)}"
  if cols = opts[:columns]
    sql << literal(Array(cols))
  end
  sql << " FROM STDIN"
  if opts[:options] || opts[:format]
    sql << " ("
    sql << "FORMAT #{opts[:format]}" if opts[:format]
    sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
    sql << ')'
  end
  sql
end
copy_table_sql(table, opts) click to toggle source

SQL for doing fast table output to stdout.

# File lib/sequel/adapters/shared/postgres.rb, line 1336
def copy_table_sql(table, opts)
  if table.is_a?(String)
    table
  else
    if opts[:options] || opts[:format]
      options = String.new
      options << " ("
      options << "FORMAT #{opts[:format]}" if opts[:format]
      options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
      options << ')'
    end
    table = if table.is_a?(::Sequel::Dataset)
      "(#{table.sql})"
    else
      literal(table)
    end
    "COPY #{table} TO STDOUT#{options}"
  end
end
create_function_sql(name, definition, opts=OPTS) click to toggle source

SQL statement to create database function.

# File lib/sequel/adapters/shared/postgres.rb, line 1357
      def create_function_sql(name, definition, opts=OPTS)
        args = opts[:args]
        if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)}
          returns = opts[:returns] || 'void'
        end
        language = opts[:language] || 'SQL'
        <<-END
        CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
        #{"RETURNS #{returns}" if returns}
        LANGUAGE #{language}
        #{opts[:behavior].to_s.upcase if opts[:behavior]}
        #{'STRICT' if opts[:strict]}
        #{'SECURITY DEFINER' if opts[:security_definer]}
        #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]}
        #{"COST #{opts[:cost]}" if opts[:cost]}
        #{"ROWS #{opts[:rows]}" if opts[:rows]}
        #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
        AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
        END
      end
create_language_sql(name, opts=OPTS) click to toggle source

SQL for creating a procedural language.

# File lib/sequel/adapters/shared/postgres.rb, line 1379
def create_language_sql(name, opts=OPTS)
  "CREATE#{' OR REPLACE' if opts[:replace] && server_version >= 90000}#{' TRUSTED' if opts[:trusted]} LANGUAGE #{name}#{" HANDLER #{opts[:handler]}" if opts[:handler]}#{" VALIDATOR #{opts[:validator]}" if opts[:validator]}"
end
create_partition_of_table_from_generator(name, generator, options) click to toggle source

Create a partition of another table, used when the #create_table with the :partition_of option is given.

# File lib/sequel/adapters/shared/postgres.rb, line 1385
def create_partition_of_table_from_generator(name, generator, options)
  execute_ddl(create_partition_of_table_sql(name, generator, options))
end
create_partition_of_table_sql(name, generator, options) click to toggle source

SQL for creating a partition of another table.

# File lib/sequel/adapters/shared/postgres.rb, line 1390
def create_partition_of_table_sql(name, generator, options)
  sql = create_table_prefix_sql(name, options).dup

  sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}"

  case generator.partition_type
  when :range
    from, to = generator.range
    sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}"
  when :list
    sql << " FOR VALUES IN #{literal(generator.list)}"
  when :hash
    mod, remainder = generator.hash_values
    sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})"
  else # when :default
    sql << " DEFAULT"
  end

  sql << create_table_suffix_sql(name, options)

  sql
end
create_schema_sql(name, opts=OPTS) click to toggle source

SQL for creating a schema.

# File lib/sequel/adapters/shared/postgres.rb, line 1414
def create_schema_sql(name, opts=OPTS)
  "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}"
end
create_table_as_sql(name, sql, options) click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 1472
def create_table_as_sql(name, sql, options)
  result = create_table_prefix_sql name, options
  if on_commit = options[:on_commit]
    result += " ON COMMIT #{ON_COMMIT[on_commit]}"
  end
  result += " AS #{sql}"
end
create_table_generator_class() click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 1480
def create_table_generator_class
  Postgres::CreateTableGenerator
end
create_table_prefix_sql(name, options) click to toggle source

DDL statement for creating a table with the given name, columns, and options

# File lib/sequel/adapters/shared/postgres.rb, line 1419
def create_table_prefix_sql(name, options)
  prefix_sql = if options[:temp]
    raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
    raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
    temporary_table_sql
  elsif options[:foreign]
    raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
    'FOREIGN '
  elsif options.fetch(:unlogged){typecast_value_boolean(@opts[:unlogged_tables_default])}
    'UNLOGGED '
  end

  "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}"
end
create_table_sql(name, generator, options) click to toggle source

SQL for creating a table with PostgreSQL specific options

# File lib/sequel/adapters/shared/postgres.rb, line 1435
def create_table_sql(name, generator, options)
  "#{super}#{create_table_suffix_sql(name, options)}"
end
create_table_suffix_sql(name, options) click to toggle source

Handle various PostgreSQl specific table extensions such as inheritance, partitioning, tablespaces, and foreign tables.

# File lib/sequel/adapters/shared/postgres.rb, line 1441
def create_table_suffix_sql(name, options)
  sql = String.new

  if inherits = options[:inherits]
    sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
  end

  if partition_by = options[:partition_by]
    sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
  end

  if on_commit = options[:on_commit]
    raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
    raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
    sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
  end

  if tablespace = options[:tablespace]
    sql << " TABLESPACE #{quote_identifier(tablespace)}"
  end

  if server = options[:foreign]
    sql << " SERVER #{quote_identifier(server)}"
    if foreign_opts = options[:options]
      sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
    end
  end

  sql
end
create_trigger_sql(table, name, function, opts=OPTS) click to toggle source

SQL for creating a database trigger.

# File lib/sequel/adapters/shared/postgres.rb, line 1485
def create_trigger_sql(table, name, function, opts=OPTS)
  events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
  whence = opts[:after] ? 'AFTER' : 'BEFORE'
  if filter = opts[:when]
    raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
    filter = " WHEN #{filter_expr(filter)}"
  end
  "CREATE #{'OR REPLACE ' if opts[:replace]}TRIGGER #{name} #{whence} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)}#{' FOR EACH ROW' if opts[:each_row]}#{filter} EXECUTE PROCEDURE #{function}(#{Array(opts[:args]).map{|a| literal(a)}.join(', ')})"
end
create_view_prefix_sql(name, options) click to toggle source

DDL fragment for initial part of CREATE VIEW statement

# File lib/sequel/adapters/shared/postgres.rb, line 1496
def create_view_prefix_sql(name, options)
  sql = create_view_sql_append_columns("CREATE #{'OR REPLACE 'if options[:replace]}#{'TEMPORARY 'if options[:temp]}#{'RECURSIVE ' if options[:recursive]}#{'MATERIALIZED ' if options[:materialized]}VIEW #{quote_schema_table(name)}", options[:columns] || options[:recursive])

  if options[:security_invoker]
    sql += " WITH (security_invoker)"
  end

  if tablespace = options[:tablespace]
    sql += " TABLESPACE #{quote_identifier(tablespace)}"
  end

  sql
end
database_error_regexps() click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 1314
def database_error_regexps
  DATABASE_ERROR_REGEXPS
end
database_specific_error_class_from_sqlstate(sqlstate) click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1290
def database_specific_error_class_from_sqlstate(sqlstate)
  if sqlstate == '23P01'
    ExclusionConstraintViolation
  elsif sqlstate == '40P01'
    SerializationFailure
  elsif sqlstate == '55P03'
    DatabaseLockTimeout
  else
    super
  end
end
drop_function_sql(name, opts=OPTS) click to toggle source

SQL for dropping a function from the database.

# File lib/sequel/adapters/shared/postgres.rb, line 1511
def drop_function_sql(name, opts=OPTS)
  "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
end
drop_index_sql(table, op) click to toggle source

Support :if_exists, :cascade, and :concurrently options.

# File lib/sequel/adapters/shared/postgres.rb, line 1516
def drop_index_sql(table, op)
  sch, _ = schema_and_table(table)
  "DROP INDEX#{' CONCURRENTLY' if op[:concurrently]}#{' IF EXISTS' if op[:if_exists]} #{"#{quote_identifier(sch)}." if sch}#{quote_identifier(op[:name] || default_index_name(table, op[:columns]))}#{' CASCADE' if op[:cascade]}"
end
drop_language_sql(name, opts=OPTS) click to toggle source

SQL for dropping a procedural language from the database.

# File lib/sequel/adapters/shared/postgres.rb, line 1522
def drop_language_sql(name, opts=OPTS)
  "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
end
drop_schema_sql(name, opts=OPTS) click to toggle source

SQL for dropping a schema from the database.

# File lib/sequel/adapters/shared/postgres.rb, line 1527
def drop_schema_sql(name, opts=OPTS)
  "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
end
drop_table_sql(name, options) click to toggle source

Support :foreign tables

# File lib/sequel/adapters/shared/postgres.rb, line 1537
def drop_table_sql(name, options)
  "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
end
drop_trigger_sql(table, name, opts=OPTS) click to toggle source

SQL for dropping a trigger from the database.

# File lib/sequel/adapters/shared/postgres.rb, line 1532
def drop_trigger_sql(table, name, opts=OPTS)
  "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
end
drop_view_sql(name, opts=OPTS) click to toggle source

SQL for dropping a view from the database.

# File lib/sequel/adapters/shared/postgres.rb, line 1542
def drop_view_sql(name, opts=OPTS)
  "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
end
filter_schema(ds, opts) click to toggle source

If opts includes a :schema option, use it, otherwise restrict the filter to only the currently visible schemas.

# File lib/sequel/adapters/shared/postgres.rb, line 1548
def filter_schema(ds, opts)
  expr = if schema = opts[:schema]
    if schema.is_a?(SQL::Identifier)
      schema.value.to_s
    else
      schema.to_s
    end
  else
    Sequel.function(:any, Sequel.function(:current_schemas, false))
  end
  ds.where{{pg_namespace[:nspname]=>expr}}
end
index_definition_sql(table_name, index) click to toggle source
# File lib/sequel/adapters/shared/postgres.rb, line 1561
def index_definition_sql(table_name, index)
  cols = index[:columns]
  index_name = index[:name] || default_index_name(table_name, cols)

  expr = if o = index[:opclass]
    "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
  else
    literal(Array(cols))
  end

  if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
  unique = "UNIQUE " if index[:unique]
  index_type = index[:type]
  filter = index[:where] || index[:filter]
  filter = " WHERE #{filter_expr(filter)}" if filter
  nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil?

  case index_type
  when :full_text
    expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
    index_type = index[:index_type] || :gin
  when :spatial
    index_type = :gist
  end

  "CREATE #{unique}INDEX#{' CONCURRENTLY' if index[:concurrently]}#{if_not_exists} #{quote_identifier(index_name)} ON #{quote_schema_table(table_name)} #{"USING #{index_type} " if index_type}#{expr}#{" INCLUDE #{literal(Array(index[:include]))}" if index[:include]}#{nulls_distinct}#{" TABLESPACE #{quote_identifier(index[:tablespace])}" if index[:tablespace]}#{filter}"
end
initialize_postgres_adapter() click to toggle source

Setup datastructures shared by all postgres adapters.

# File lib/sequel/adapters/shared/postgres.rb, line 1590
def initialize_postgres_adapter
  @primary_keys = {}
  @primary_key_sequences = {}
  @supported_types = {}
  procs = @conversion_procs = CONVERSION_PROCS.dup
  procs[1184] = procs[1114] = method(:to_application_timestamp)
end
pg_class_relname(type, opts) { || ... } click to toggle source

Backbone of the tables and views support.

# File lib/sequel/adapters/shared/postgres.rb, line 1599
def pg_class_relname(type, opts)
  ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
  ds = filter_schema(ds, opts)
  m = output_identifier_meth
  if defined?(yield)
    yield(ds)
  elsif opts[:qualify]
    ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
  else
    ds.map{|r| m.call(r[:relname])}
  end
end
regclass_oid(expr, opts=OPTS) click to toggle source

Return an expression the oid for the table expr. Used by the metadata parsing code to disambiguate unqualified tables.

# File lib/sequel/adapters/shared/postgres.rb, line 1614
def regclass_oid(expr, opts=OPTS)
  if expr.is_a?(String) && !expr.is_a?(LiteralString)
    expr = Sequel.identifier(expr)
  end

  sch, table = schema_and_table(expr)
  sch ||= opts[:schema]
  if sch
    expr = Sequel.qualify(sch, table)
  end
  
  expr = if ds = opts[:dataset]
    ds.literal(expr)
  else
    literal(expr)
  end

  Sequel.cast(expr.to_s,:regclass).cast(:oid)
end
remove_cached_schema(table) click to toggle source

Remove the cached entries for primary keys and sequences when a table is changed.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1635
def remove_cached_schema(table)
  tab = quote_schema_table(table)
  Sequel.synchronize do
    @primary_keys.delete(tab)
    @primary_key_sequences.delete(tab)
  end
  super
end
rename_table_sql(name, new_name) click to toggle source

SQL DDL statement for renaming a table. PostgreSQL doesn't allow you to change a table's schema in a rename table operation, so specifying a new schema in new_name will not have an effect.

# File lib/sequel/adapters/shared/postgres.rb, line 1646
def rename_table_sql(name, new_name)
  "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
end
schema_array_type(db_type) click to toggle source

The schema :type entry to use for array types.

# File lib/sequel/adapters/shared/postgres.rb, line 1663
def schema_array_type(db_type)
  :array
end
schema_column_type(db_type) click to toggle source

Handle interval and citext types.

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1651
def schema_column_type(db_type)
  case db_type
  when /\Ainterval\z/io
    :interval
  when /\Acitext\z/io
    :string
  else
    super
  end
end
schema_composite_type(db_type) click to toggle source

The schema :type entry to use for row/composite types.

# File lib/sequel/adapters/shared/postgres.rb, line 1668
def schema_composite_type(db_type)
  :composite
end
schema_enum_type(db_type) click to toggle source

The schema :type entry to use for enum types.

# File lib/sequel/adapters/shared/postgres.rb, line 1673
def schema_enum_type(db_type)
  :enum
end
schema_multirange_type(db_type) click to toggle source

The schema :type entry to use for multirange types.

# File lib/sequel/adapters/shared/postgres.rb, line 1683
def schema_multirange_type(db_type)
  :multirange
end
schema_parse_table(table_name, opts) click to toggle source

The dataset used for parsing table schemas, using the pg_* system catalogs.

# File lib/sequel/adapters/shared/postgres.rb, line 1700
def schema_parse_table(table_name, opts)
  m = output_identifier_meth(opts[:dataset])

  _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row|
    row[:default] = nil if blank_object?(row[:default])
    if row[:base_oid]
      row[:domain_oid] = row[:oid]
      row[:oid] = row.delete(:base_oid)
      row[:db_domain_type] = row[:db_type]
      row[:db_type] = row.delete(:db_base_type)
    else
      row.delete(:base_oid)
      row.delete(:db_base_type)
    end

    db_type = row[:db_type]
    row[:type] = if row.delete(:is_array)
      schema_array_type(db_type)
    else
      send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type)
    end
    identity = row.delete(:attidentity)
    if row[:primary_key]
      row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
    end

    # :nocov:
    if server_version >= 90600
    # :nocov:
      case row[:oid]
      when 1082
        row[:min_value] = MIN_DATE
        row[:max_value] = MAX_DATE
      when 1184, 1114
        if Sequel.datetime_class == Time
          row[:min_value] = MIN_TIMESTAMP
          row[:max_value] = MAX_TIMESTAMP
        end
      end
    end

    [m.call(row.delete(:name)), row]
  end
end
schema_range_type(db_type) click to toggle source

The schema :type entry to use for range types.

# File lib/sequel/adapters/shared/postgres.rb, line 1678
def schema_range_type(db_type)
  :range
end
set_transaction_isolation(conn, opts) click to toggle source

Set the transaction isolation level on the given connection

# File lib/sequel/adapters/shared/postgres.rb, line 1746
def set_transaction_isolation(conn, opts)
  level = opts.fetch(:isolation, transaction_isolation_level)
  read_only = opts[:read_only]
  deferrable = opts[:deferrable]
  if level || !read_only.nil? || !deferrable.nil?
    sql = String.new
    sql << "SET TRANSACTION"
    sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
    sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
    sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
    log_connection_execute(conn, sql)
  end
end
sql_function_args(args) click to toggle source

Turns an array of argument specifiers into an SQL fragment used for function arguments. See create_function_sql.

# File lib/sequel/adapters/shared/postgres.rb, line 1761
def sql_function_args(args)
  "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
end
supports_combining_alter_table_ops?() click to toggle source

PostgreSQL can combine multiple alter table ops into a single query.

# File lib/sequel/adapters/shared/postgres.rb, line 1766
def supports_combining_alter_table_ops?
  true
end
supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

# File lib/sequel/adapters/shared/postgres.rb, line 1771
def supports_create_or_replace_view?
  true
end
type_literal_generic_bignum_symbol(column) click to toggle source

Handle bigserial type if :serial option is present

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1776
def type_literal_generic_bignum_symbol(column)
  column[:serial] ? :bigserial : super
end
type_literal_generic_file(column) click to toggle source

PostgreSQL uses the bytea data type for blobs

# File lib/sequel/adapters/shared/postgres.rb, line 1781
def type_literal_generic_file(column)
  :bytea
end
type_literal_generic_integer(column) click to toggle source

Handle serial type if :serial option is present

Calls superclass method
# File lib/sequel/adapters/shared/postgres.rb, line 1786
def type_literal_generic_integer(column)
  column[:serial] ? :serial : super
end
type_literal_generic_string(column) click to toggle source

PostgreSQL prefers the text datatype. If a fixed size is requested, the char type is used. If the text type is specifically disallowed or there is a size specified, use the varchar type. Otherwise use the text type.

# File lib/sequel/adapters/shared/postgres.rb, line 1794
def type_literal_generic_string(column)
  if column[:text]
    :text
  elsif column[:fixed]
    "char(#{column[:size]||default_string_column_size})"
  elsif column[:text] == false || column[:size]
    "varchar(#{column[:size]||default_string_column_size})"
  else
    :text
  end
end
view_with_check_option_support() click to toggle source

PostgreSQL 9.4+ supports views with check option.

# File lib/sequel/adapters/shared/postgres.rb, line 1807
def view_with_check_option_support
  # :nocov:
  :local if server_version >= 90400
  # :nocov:
end