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

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)

Source

    # File lib/sequel/adapters/shared/postgres.rb
327 def add_conversion_proc(oid, callable=nil, &block)
328   conversion_procs[oid] = callable || block
329 end

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

add_named_conversion_proc (name, &block)

Source

    # File lib/sequel/adapters/shared/postgres.rb
334 def add_named_conversion_proc(name, &block)
335   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
336     raise Error, "No matching type in pg_type for #{name.inspect}"
337   end
338   add_conversion_proc(oid, block)
339 end

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.

check_constraints (table)

Source

    # File lib/sequel/adapters/shared/postgres.rb
350 def check_constraints(table)
351   m = output_identifier_meth
352 
353   hash = {}
354   _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row|
355     constraint = m.call(row[:constraint])
356     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[], :validated=>row[:validated], :enforced=>row[:enforced]}
357     entry[:columns] << m.call(row[:column]) if row[:column]
358   end
359   
360   hash
361 end

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 determine the column symbols.

commit_prepared_transaction (transaction_id, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
341 def commit_prepared_transaction(transaction_id, opts=OPTS)
342   run("COMMIT PREPARED #{literal(transaction_id)}".freeze, opts)
343 end

convert_serial_to_identity (table, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
381 def convert_serial_to_identity(table, opts=OPTS)
382   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
383 
384   server = opts[:server]
385   server_hash = server ? {:server=>server} : OPTS
386   ds = dataset
387   ds = ds.server(server) if server
388 
389   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
390 
391   table_oid = regclass_oid(table)
392   im = input_identifier_meth
393   unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
394     raise Error, "could not determine column to convert from serial to identity automatically"
395   end
396   column = im.call(column)
397 
398   column_num = ds.from(:pg_attribute).
399     where(:attrelid=>table_oid, :attname=>column).
400     get(:attnum)
401 
402   pg_class = Sequel.cast('pg_class', :regclass)
403   res = ds.from(:pg_depend).
404     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
405     select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])
406 
407   case res.length
408   when 0
409     raise Error, "unable to find related sequence when converting serial to identity"
410   when 1
411     seq_oid, already_identity = res.first
412   else
413     raise Error, "more than one linked sequence found when converting serial to identity"
414   end
415 
416   return if already_identity
417 
418   transaction(server_hash) do
419     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT".freeze, server_hash)
420 
421     ds.from(:pg_depend).
422       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
423       update(:deptype=>'i')
424 
425     ds.from(:pg_attribute).
426       where(:attrelid=>table_oid, :attname=>column).
427       update(:attidentity=>'d')
428   end
429 
430   remove_cached_schema(table)
431   nil
432 end

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

create_function (name, definition, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
455 def create_function(name, definition, opts=OPTS)
456   self << create_function_sql(name, definition, opts).freeze
457 end

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.

create_language (name, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
466 def create_language(name, opts=OPTS)
467   self << create_language_sql(name, opts).freeze
468 end

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.

create_schema (name, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
475 def create_schema(name, opts=OPTS)
476   self << create_schema_sql(name, opts).freeze
477 end

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)

create_table (name, options=OPTS, &block)

Source

    # File lib/sequel/adapters/shared/postgres.rb
480 def create_table(name, options=OPTS, &block)
481   if options[:partition_of]
482     create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
483     return
484   end
485 
486   super
487 end

Support partitions of tables using the :partition_of option.

Calls superclass method

create_table? (name, options=OPTS, &block)

Source

    # File lib/sequel/adapters/shared/postgres.rb
490 def create_table?(name, options=OPTS, &block)
491   if options[:partition_of]
492     create_table(name, options.merge!(:if_not_exists=>true), &block)
493     return
494   end
495 
496   super
497 end

Support partitions of tables using the :partition_of option.

Calls superclass method

create_trigger (table, name, function, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
511 def create_trigger(table, name, function, opts=OPTS)
512   self << create_trigger_sql(table, name, function, opts).freeze
513 end

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

database_type ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
515 def database_type
516   :postgres
517 end

defer_constraints (opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
534 def defer_constraints(opts=OPTS)
535   _set_constraints(' DEFERRED', opts)
536 end

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

do (code, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
543 def do(code, opts=OPTS)
544   language = opts[:language]
545   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}".freeze
546 end

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.

drop_function (name, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
554 def drop_function(name, opts=OPTS)
555   self << drop_function_sql(name, opts).freeze
556 end

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.

drop_language (name, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
563 def drop_language(name, opts=OPTS)
564   self << drop_language_sql(name, opts).freeze
565 end

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.

drop_schema (name, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
572 def drop_schema(name, opts=OPTS)
573   self << drop_schema_sql(name, opts).freeze
574   remove_all_cached_schemas
575 end

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.

drop_trigger (table, name, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
583 def drop_trigger(table, name, opts=OPTS)
584   self << drop_trigger_sql(table, name, opts).freeze
585 end

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.

foreign_key_list (table, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
597 def foreign_key_list(table, opts=OPTS)
598   m = output_identifier_meth
599   schema, _ = opts.fetch(:schema, schema_and_table(table))
600 
601   h = {}
602   fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
603   reverse = opts[:reverse]
604 
605   (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row|
606     if reverse
607       key = [row[:schema], row[:table], row[:name]]
608     else
609       key = row[:name]
610     end
611 
612     if r = h[key]
613       r[:columns] << m.call(row[:column])
614       r[:key] << m.call(row[:refcolumn])
615     else
616       entry = h[key] = {
617         :name=>m.call(row[:name]),
618         :columns=>[m.call(row[:column])],
619         :key=>[m.call(row[:refcolumn])],
620         :on_update=>fklod_map[row[:on_update]],
621         :on_delete=>fklod_map[row[:on_delete]],
622         :deferrable=>row[:deferrable],
623         :validated=>row[:validated],
624         :enforced=>row[:enforced],
625         :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
626       }
627 
628       unless schema
629         # If not combining schema information into the :table entry
630         # include it as a separate entry.
631         entry[:schema] = m.call(row[:schema])
632       end
633     end
634   end
635 
636   h.values
637 end

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.

freeze ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
639 def freeze
640   server_version
641   supports_prepared_transactions?
642   _schema_ds
643   _select_serial_sequence_ds
644   _select_custom_sequence_ds
645   _select_pk_ds
646   _indexes_ds
647   _check_constraints_ds
648   _foreign_key_list_ds
649   _reverse_foreign_key_list_ds
650   @conversion_procs.freeze
651   super
652 end

Calls superclass method

immediate_constraints (opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
668 def immediate_constraints(opts=OPTS)
669   _set_constraints(' IMMEDIATE', opts)
670 end

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

indexes (table, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
678 def indexes(table, opts=OPTS)
679   m = output_identifier_meth
680   cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)}
681   cond[:indpred] = nil unless opts[:include_partial]
682 
683   case opts[:invalid]
684   when true, :only
685     cond[:indisvalid] = false
686   when :include
687     # nothing
688   else
689     cond[:indisvalid] = true
690   end
691 
692   indexes = {}
693   _indexes_ds.where_each(cond) do |r|
694     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
695     i[:columns] << m.call(r[:column])
696   end
697   indexes
698 end

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

:include_partial: Set to true to include partial indexes
:invalid: Set to true or :only to only return invalid indexes. Set to :include to also return both valid and invalid indexes. When not set or other value given, does not return invalid indexes.

locks ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
701 def locks
702   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
703 end

Dataset containing all current database locks

notify (channel, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
711 def notify(channel, opts=OPTS)
712   sql = String.new
713   sql << "NOTIFY "
714   dataset.send(:identifier_append, sql, channel)
715   if payload = opts[:payload]
716     sql << ", "
717     dataset.literal_append(sql, payload.to_s)
718   end
719   execute_ddl(sql, opts)
720 end

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.

primary_key (table, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
723 def primary_key(table, opts=OPTS)
724   quoted_table = quote_schema_table(table)
725   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
726   value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts))
727   Sequel.synchronize{@primary_keys[quoted_table] = value}
728 end

Return primary key for the given table.

primary_key_sequence (table, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
731 def primary_key_sequence(table, opts=OPTS)
732   quoted_table = quote_schema_table(table)
733   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
734   cond = {Sequel[:t][:oid] => regclass_oid(table, opts)}
735   value = if pks = _select_serial_sequence_ds.first(cond)
736     literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
737   elsif pks = _select_custom_sequence_ds.first(cond)
738     literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
739   end
740 
741   Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value
742 end

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

refresh_view (name, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
758 def refresh_view(name, opts=OPTS)
759   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}".freeze
760 end

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

rename_schema (name, new_name)

Source

    # File lib/sequel/adapters/shared/postgres.rb
747 def rename_schema(name, new_name)
748   self << rename_schema_sql(name, new_name).freeze
749   remove_all_cached_schemas
750 end

Rename a schema in the database. Arguments:

name: Current name of the schema
opts: New name for the schema

reset_primary_key_sequence (table)

Source

    # File lib/sequel/adapters/shared/postgres.rb
764 def reset_primary_key_sequence(table)
765   return unless seq = primary_key_sequence(table)
766   pk = SQL::Identifier.new(primary_key(table))
767   db = self
768   s, t = schema_and_table(table)
769   table = Sequel.qualify(s, t) if s
770 
771   if server_version >= 100000
772     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq.freeze)))
773     increment_by = :seqincrement
774     min_value = :seqmin
775   # simplecov:disable
776   else
777     seq_ds = metadata_dataset.from(LiteralString.new(seq))
778     increment_by = :increment_by
779     min_value = :min_value
780   # simplecov:enable
781   end
782 
783   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
784 end

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

rollback_prepared_transaction (transaction_id, opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
786 def rollback_prepared_transaction(transaction_id, opts=OPTS)
787   run("ROLLBACK PREPARED #{literal(transaction_id)}".freeze, opts)
788 end

serial_primary_key_options ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
792 def serial_primary_key_options
793   # simplecov:disable
794   auto_increment_key = server_version >= 100002 ? :identity : :serial
795   # simplecov:enable
796   {:primary_key => true, auto_increment_key => true, :type=>Integer}
797 end

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

server_version (server=nil)

Source

    # File lib/sequel/adapters/shared/postgres.rb
800 def server_version(server=nil)
801   return @server_version if @server_version
802   ds = dataset
803   ds = ds.server(server) if server
804   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
805 end

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

supports_create_table_if_not_exists? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
808 def supports_create_table_if_not_exists?
809   server_version >= 90100
810 end

PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+

supports_deferrable_constraints? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
813 def supports_deferrable_constraints?
814   server_version >= 90000
815 end

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

supports_deferrable_foreign_key_constraints? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
818 def supports_deferrable_foreign_key_constraints?
819   true
820 end

PostgreSQL supports deferrable foreign key constraints.

supports_drop_table_if_exists? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
823 def supports_drop_table_if_exists?
824   true
825 end

PostgreSQL supports DROP TABLE IF EXISTS

supports_partial_indexes? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
828 def supports_partial_indexes?
829   true
830 end

PostgreSQL supports partial indexes.

supports_prepared_transactions? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
839 def supports_prepared_transactions?
840   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
841   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
842 end

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

supports_savepoints? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
845 def supports_savepoints?
846   true
847 end

PostgreSQL supports savepoints

supports_transaction_isolation_levels? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
850 def supports_transaction_isolation_levels?
851   true
852 end

PostgreSQL supports transaction isolation levels

supports_transactional_ddl? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
855 def supports_transactional_ddl?
856   true
857 end

PostgreSQL supports transaction DDL statements.

supports_trigger_conditions? ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
833 def supports_trigger_conditions?
834   server_version >= 90000
835 end

PostgreSQL 9.0+ supports trigger conditions.

tables (opts=OPTS, &block)

Source

    # File lib/sequel/adapters/shared/postgres.rb
868 def tables(opts=OPTS, &block)
869   pg_class_relname(['r', 'p'], opts, &block)
870 end

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

type_supported? (type)

Source

    # File lib/sequel/adapters/shared/postgres.rb
874 def type_supported?(type)
875   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
876   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
877   Sequel.synchronize{return @supported_types[type] = supported}
878 end

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

values (v)

Source

    # File lib/sequel/adapters/shared/postgres.rb
887 def values(v)
888   raise Error, "Cannot provide an empty array for values" if v.empty?
889   @default_dataset.clone(:values=>v)
890 end

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

views (opts=OPTS)

Source

    # File lib/sequel/adapters/shared/postgres.rb
900 def views(opts=OPTS)
901   relkind = opts[:materialized] ? 'm' : 'v'
902   pg_class_relname(relkind, opts)
903 end

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

with_advisory_lock (lock_id, opts=OPTS) { || ... }

Source

    # File lib/sequel/adapters/shared/postgres.rb
916 def with_advisory_lock(lock_id, opts=OPTS)
917   ds = dataset
918   if server = opts[:server]
919     ds = ds.server(server)
920   end
921 
922   synchronize(server) do |c|
923     begin
924       if opts[:wait]
925         ds.get{pg_advisory_lock(lock_id)}
926         locked = true
927       else
928         unless locked = ds.get{pg_try_advisory_lock(lock_id)}
929           raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}"
930         end
931       end
932 
933       yield
934     ensure
935       ds.get{pg_advisory_unlock(lock_id)} if locked
936     end
937   end
938 end

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.

Private Instance Methods

__foreign_key_list_ds (reverse)

Source

     # File lib/sequel/adapters/shared/postgres.rb
 966 def __foreign_key_list_ds(reverse)
 967   if reverse
 968     ctable = Sequel[:att2]
 969     cclass = Sequel[:cl2]
 970     rtable = Sequel[:att]
 971     rclass = Sequel[:cl]
 972   else
 973     ctable = Sequel[:att]
 974     cclass = Sequel[:cl]
 975     rtable = Sequel[:att2]
 976     rclass = Sequel[:cl2]
 977   end
 978 
 979   if server_version >= 90500
 980     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
 981     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
 982   # simplecov:disable
 983   else
 984     range = 0...32
 985     cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
 986     rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
 987   # simplecov:enable
 988   end
 989 
 990   ds = metadata_dataset.
 991     from{pg_constraint.as(:co)}.
 992     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
 993     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
 994     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
 995     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
 996     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
 997     order{[co[:conname], cpos]}.
 998     where{{
 999       cl[:relkind]=>%w'r p',
1000       co[:contype]=>'f',
1001       cpos=>rpos
1002     }}.
1003     select{[
1004       co[:conname].as(:name),
1005       ctable[:attname].as(:column),
1006       co[:confupdtype].as(:on_update),
1007       co[:confdeltype].as(:on_delete),
1008       cl2[:relname].as(:table),
1009       rtable[:attname].as(:refcolumn),
1010       SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
1011       nsp[:nspname].as(:schema)
1012     ]}
1013 
1014   if reverse
1015     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
1016   end
1017 
1018   _add_validated_enforced_constraint_columns(ds)
1019 end

Build dataset used for foreign key list methods.

_add_validated_enforced_constraint_columns (ds)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1021 def _add_validated_enforced_constraint_columns(ds)
1022   validated_cond = if server_version >= 90100
1023     Sequel[:convalidated]
1024   # simplecov:disable
1025   else
1026     Sequel.cast(true, TrueClass)
1027   # simplecov:enable
1028   end
1029   ds = ds.select_append(validated_cond.as(:validated))
1030 
1031   enforced_cond = if server_version >= 180000
1032     Sequel[:conenforced]
1033   # simplecov:disable
1034   else
1035     Sequel.cast(true, TrueClass)
1036   # simplecov:enable
1037   end
1038   ds = ds.select_append(enforced_cond.as(:enforced))
1039 
1040   ds
1041 end

_check_constraints_ds ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
943 def _check_constraints_ds
944   @_check_constraints_ds ||= begin
945     ds = metadata_dataset.
946       from{pg_constraint.as(:co)}.
947       left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
948       where(:contype=>'c').
949       select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
950 
951     _add_validated_enforced_constraint_columns(ds)
952   end
953 end

Dataset used to retrieve CHECK constraint information

_foreign_key_list_ds ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
956 def _foreign_key_list_ds
957   @_foreign_key_list_ds ||= __foreign_key_list_ds(false)
958 end

Dataset used to retrieve foreign keys referenced by a table

_indexes_ds ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1044 def _indexes_ds
1045   @_indexes_ds ||= begin
1046     if server_version >= 90500
1047       order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
1048     # simplecov:disable
1049     else
1050       range = 0...32
1051       order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
1052     # simplecov:enable
1053     end
1054 
1055     attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey])
1056 
1057     ds = metadata_dataset.
1058       from{pg_class.as(:tab)}.
1059       join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
1060       join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
1061       join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
1062       left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
1063       where{{
1064         indc[:relkind]=>%w'i I',
1065         ind[:indisprimary]=>false,
1066         :indexprs=>nil}}.
1067       order(*order).
1068       select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}
1069 
1070     # simplecov:disable
1071     ds = ds.where(:indisready=>true) if server_version >= 80300
1072     ds = ds.where(:indislive=>true) if server_version >= 90300
1073     # simplecov:enable
1074 
1075     ds
1076   end
1077 end

Dataset used to retrieve index information

_reverse_foreign_key_list_ds ()

Source

    # File lib/sequel/adapters/shared/postgres.rb
961 def _reverse_foreign_key_list_ds
962   @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true)
963 end

Dataset used to retrieve foreign keys referencing a table

_schema_ds ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1140 def _schema_ds
1141   @_schema_ds ||= begin
1142     ds = metadata_dataset.select{[
1143         pg_attribute[:attname].as(:name),
1144         SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid),
1145         SQL::Cast.new(basetype[:oid], :integer).as(:base_oid),
1146         SQL::Function.new(:col_description, pg_class[:oid], pg_attribute[:attnum]).as(:comment),
1147         SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type),
1148         SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type),
1149         SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default),
1150         SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null),
1151         SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key),
1152         Sequel[:pg_type][:typtype],
1153         (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array),
1154       ]}.
1155       from(:pg_class).
1156       join(:pg_attribute, :attrelid=>:oid).
1157       join(:pg_type, :oid=>:atttypid).
1158       left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype).
1159       left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]).
1160       left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]).
1161       left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true).
1162       where{{pg_attribute[:attisdropped]=>false}}.
1163       where{pg_attribute[:attnum] > 0}.
1164       order{pg_attribute[:attnum]}
1165 
1166     # simplecov:disable
1167     if server_version > 100000
1168     # simplecov:enable
1169       ds = ds.select_append{pg_attribute[:attidentity]}
1170 
1171       # simplecov:disable
1172       if server_version > 120000
1173       # simplecov:enable
1174         ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)}
1175       end
1176     end
1177 
1178     ds
1179   end
1180 end

Dataset used to get schema for tables

_select_custom_sequence_ds ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1080 def _select_custom_sequence_ds
1081   @_select_custom_sequence_ds ||= metadata_dataset.
1082     from{pg_class.as(:t)}.
1083     join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name).
1084     join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr).
1085     join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def).
1086     join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons).
1087     where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}.
1088     select{
1089       expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2)
1090       [
1091         name[:nspname].as(:schema),
1092         Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence)
1093       ]
1094     }
1095 end

Dataset used to determine custom serial sequences for tables

_select_pk_ds ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1126 def _select_pk_ds
1127   @_select_pk_ds ||= metadata_dataset.
1128     from(:pg_class, :pg_attribute, :pg_index, :pg_namespace).
1129     where{[
1130       [pg_class[:oid], pg_attribute[:attrelid]],
1131       [pg_class[:relnamespace], pg_namespace[:oid]],
1132       [pg_class[:oid], pg_index[:indrelid]],
1133       [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]],
1134       [pg_index[:indisprimary], 't']
1135     ]}.
1136     select{pg_attribute[:attname].as(:pk)}
1137 end

Dataset used to determine primary keys for tables

_select_serial_sequence_ds ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1098 def _select_serial_sequence_ds
1099   @_serial_sequence_ds ||= metadata_dataset.
1100     from{[
1101       pg_class.as(:seq),
1102       pg_attribute.as(:attr),
1103       pg_depend.as(:dep),
1104       pg_namespace.as(:name),
1105       pg_constraint.as(:cons),
1106       pg_class.as(:t)
1107     ]}.
1108     where{[
1109       [seq[:oid], dep[:objid]],
1110       [seq[:relnamespace], name[:oid]],
1111       [seq[:relkind], 'S'],
1112       [attr[:attrelid], dep[:refobjid]],
1113       [attr[:attnum], dep[:refobjsubid]],
1114       [attr[:attrelid], cons[:conrelid]],
1115       [attr[:attnum], cons[:conkey].sql_subscript(1)],
1116       [attr[:attrelid], t[:oid]],
1117       [cons[:contype], 'p']
1118     ]}.
1119     select{[
1120       name[:nspname].as(:schema),
1121       seq[:relname].as(:sequence)
1122     ]}
1123 end

Dataset used to determine normal serial sequences for tables

_set_constraints (type, opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1183 def _set_constraints(type, opts)
1184   execute_ddl(_set_constraints_sql(type, opts), opts)
1185 end

Internals of defer_constraints/immediate_constraints

_set_constraints_sql (type, opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1188 def _set_constraints_sql(type, opts)
1189   sql = String.new
1190   sql << "SET CONSTRAINTS "
1191   if constraints = opts[:constraints]
1192     dataset.send(:source_list_append, sql, Array(constraints))
1193   else
1194     sql << "ALL"
1195   end
1196   sql << type
1197 end

SQL to use for SET CONSTRAINTS

_table_exists? (ds)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1201 def _table_exists?(ds)
1202   super
1203 rescue DatabaseError => e    
1204   raise e unless /canceling statement due to (?:statement|lock) timeout/ =~ e.message 
1205 end

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

Calls superclass method

alter_table_add_column_sql (table, op)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1207 def alter_table_add_column_sql(table, op)
1208   "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}"
1209 end

alter_table_alter_constraint_sql (table, op)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1211 def alter_table_alter_constraint_sql(table, op)
1212   sql = String.new
1213   sql << "ALTER CONSTRAINT #{quote_identifier(op[:name])}"
1214   
1215   constraint_deferrable_sql_append(sql, op[:deferrable])
1216 
1217   case op[:enforced]
1218   when nil
1219   when false
1220     sql << " NOT ENFORCED"
1221   else
1222     sql << " ENFORCED"
1223   end
1224 
1225   case op[:inherit]
1226   when nil
1227   when false
1228     sql << " NO INHERIT"
1229   else
1230     sql << " INHERIT"
1231   end
1232 
1233   sql
1234 end

alter_table_drop_column_sql (table, op)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1254 def alter_table_drop_column_sql(table, op)
1255   "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}"
1256 end

alter_table_generator_class ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1236 def alter_table_generator_class
1237   Postgres::AlterTableGenerator
1238 end

alter_table_rename_constraint_sql (table, op)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1240 def alter_table_rename_constraint_sql(table, op)
1241   "RENAME CONSTRAINT #{quote_identifier(op[:name])} TO #{quote_identifier(op[:new_name])}"
1242 end

alter_table_set_column_type_sql (table, op)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1244 def alter_table_set_column_type_sql(table, op)
1245   s = super
1246   if using = op[:using]
1247     using = Sequel::LiteralString.new(using) if using.is_a?(String)
1248     s += ' USING '
1249     s << literal(using)
1250   end
1251   s
1252 end

Calls superclass method

alter_table_validate_constraint_sql (table, op)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1258 def alter_table_validate_constraint_sql(table, op)
1259   "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
1260 end

begin_new_transaction (conn, opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1265 def begin_new_transaction(conn, opts)
1266   super
1267   if opts.has_key?(:synchronous)
1268     case sync = opts[:synchronous]
1269     when true
1270       sync = :on
1271     when false
1272       sync = :off
1273     when nil
1274       return
1275     when :on, :off, :local, :remote_apply, :remote_write
1276       nil
1277     else
1278       # SEQUEL6: raise
1279       Sequel::Deprecation.deprecate("Unsupported :synchronous option given to Database#transaction: #{sync.inspect}, this will raise an error in Sequel 6.")
1280       return
1281     end
1282 
1283     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
1284   end
1285 end

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, :remote_apply, and :remote_write.

Calls superclass method

begin_savepoint (conn, opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1288 def begin_savepoint(conn, opts)
1289   super
1290 
1291   unless (read_only = opts[:read_only]).nil?
1292     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
1293   end
1294 end

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

Calls superclass method

column_definition_add_references_sql (sql, column)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1442 def column_definition_add_references_sql(sql, column)
1443   super
1444   if column[:not_enforced]
1445     sql << " NOT ENFORCED"
1446   end
1447 end

Calls superclass method

column_definition_append_include_sql (sql, constraint)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1296 def column_definition_append_include_sql(sql, constraint)
1297   if include_cols = constraint[:include]
1298     sql << " INCLUDE " << literal(Array(include_cols))
1299   end
1300 end

column_definition_append_primary_key_sql (sql, constraint)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1302 def column_definition_append_primary_key_sql(sql, constraint)
1303   super
1304   column_definition_append_include_sql(sql, constraint)
1305 end

Calls superclass method

column_definition_append_unique_sql (sql, constraint)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1307 def column_definition_append_unique_sql(sql, constraint)
1308   super
1309   column_definition_append_include_sql(sql, constraint)
1310 end

Calls superclass method

column_definition_collate_sql (sql, column)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1314 def column_definition_collate_sql(sql, column)
1315   if collate = column[:collate]
1316     collate = literal(collate) unless collate.is_a?(String)
1317     sql << " COLLATE #{collate}"
1318   end
1319 end

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

column_definition_default_sql (sql, column)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1323 def column_definition_default_sql(sql, column)
1324   super
1325   if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
1326     if (identity = column[:identity])
1327       sql << " GENERATED "
1328       sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
1329       sql << " AS IDENTITY"
1330     elsif (generated = column[:generated_always_as])
1331       sql << " GENERATED ALWAYS AS (#{literal(generated)}) #{column[:virtual] ? 'VIRTUAL' : 'STORED'}"
1332     end
1333   end
1334 end

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

Calls superclass method

column_definition_null_sql (sql, column)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1449 def column_definition_null_sql(sql, column)
1450   constraint = column[:not_null]
1451   constraint = nil unless constraint.is_a?(Hash)
1452   if constraint && (name = constraint[:name])
1453     sql << " CONSTRAINT #{quote_identifier(name)}"
1454   end
1455   super
1456   if constraint && constraint[:no_inherit]
1457     sql << " NO INHERIT"
1458   end
1459 end

Calls superclass method

column_references_add_period (cols)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1476 def column_references_add_period(cols)
1477   cols= cols.dup
1478   cols[-1] = Sequel.lit("PERIOD #{quote_identifier(cols[-1])}".freeze)
1479   cols
1480 end

column_references_append_key_sql (sql, column)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1470 def column_references_append_key_sql(sql, column)
1471   cols = Array(column[:key])
1472   cols = column_references_add_period(cols) if column[:period]
1473   sql << "(#{cols.map{|x| quote_identifier(x)}.join(', ')})"
1474 end

column_references_table_constraint_sql (constraint)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1462 def column_references_table_constraint_sql(constraint)
1463   sql = String.new
1464   sql << "FOREIGN KEY "
1465   cols = constraint[:columns]
1466   cols = column_references_add_period(cols) if constraint[:period]
1467   sql << literal(cols) << column_references_sql(constraint)
1468 end

Handle :period option

column_schema_normalize_default (default, type)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1337 def column_schema_normalize_default(default, type)
1338   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
1339     default = m[1] || m[2]
1340   end
1341   super(default, type)
1342 end

Handle PostgreSQL specific default format.

Calls superclass method

combinable_alter_table_op? (op)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1356 def combinable_alter_table_op?(op)
1357   (super || op[:op] == :validate_constraint || op[:op] == :alter_constraint) && op[:op] != :rename_column
1358 end

PostgreSQL can’t combine rename_column operations, and it can combine validate_constraint and alter_constraint operations.

Calls superclass method

commit_transaction (conn, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1346 def commit_transaction(conn, opts=OPTS)
1347   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
1348     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
1349   else
1350     super
1351   end
1352 end

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

Calls superclass method

connection_configuration_sqls (opts=@opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1362 def connection_configuration_sqls(opts=@opts)
1363   sqls = []
1364 
1365   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
1366 
1367   cmm = opts.fetch(:client_min_messages, :warning)
1368   if cmm && !cmm.to_s.empty?
1369     cmm = cmm.to_s.upcase.strip
1370     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
1371       raise Error, "Unsupported client_min_messages setting: #{cmm}"
1372     end
1373     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
1374   end
1375 
1376   if search_path = opts[:search_path]
1377     case search_path
1378     when String
1379       search_path = search_path.split(",").map(&:strip)
1380     when Array
1381       # nil
1382     else
1383       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
1384     end
1385     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
1386   end
1387 
1388   sqls
1389 end

The SQL queries to execute when starting a new connection.

constraint_definition_sql (constraint)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1392 def constraint_definition_sql(constraint)
1393   case type = constraint[:type]
1394   when :exclude
1395     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
1396     sql = String.new
1397     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
1398     sql << "EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})"
1399     column_definition_append_include_sql(sql, constraint)
1400     sql << " WHERE #{filter_expr(constraint[:where])}" if constraint[:where]
1401     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1402     sql
1403   when :primary_key, :unique
1404     sql = String.new
1405     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
1406 
1407     if type == :primary_key
1408       sql << primary_key_constraint_sql_fragment(constraint)
1409     else
1410       sql << unique_constraint_sql_fragment(constraint)
1411     end
1412 
1413     if using_index = constraint[:using_index]
1414       sql << " USING INDEX " << quote_identifier(using_index)
1415     else
1416       cols = literal(constraint[:columns])
1417       cols.insert(-2, " WITHOUT OVERLAPS") if constraint[:without_overlaps]
1418       sql << " " << cols
1419 
1420       if include_cols = constraint[:include]
1421         sql << " INCLUDE " << literal(Array(include_cols))
1422       end
1423     end
1424 
1425     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1426     sql
1427   else # when :foreign_key, :check
1428     sql = super
1429     if constraint[:no_inherit]
1430       sql << " NO INHERIT"
1431     end
1432     if constraint[:not_enforced]
1433       sql << " NOT ENFORCED"
1434     end
1435     if constraint[:not_valid]
1436       sql << " NOT VALID"
1437     end
1438     sql
1439   end
1440 end

Handle PostgreSQL-specific constraint features.

Calls superclass method

copy_into_sql (table, opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1511 def copy_into_sql(table, opts)
1512   sql = String.new
1513   sql << "COPY #{literal(table)}"
1514   if cols = opts[:columns]
1515     sql << literal(Array(cols))
1516   end
1517   sql << " FROM STDIN"
1518   if opts[:options] || opts[:format]
1519     sql << " ("
1520     sql << "FORMAT #{opts[:format]}" if opts[:format]
1521     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1522     sql << ')'
1523   end
1524   sql
1525 end

SQL for doing fast table insert from stdin.

copy_table_sql (table, opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1528 def copy_table_sql(table, opts)
1529   if table.is_a?(String)
1530     table
1531   else
1532     if opts[:options] || opts[:format]
1533       options = String.new
1534       options << " ("
1535       if format = opts[:format]
1536         sql_format = format.to_s.downcase
1537         unless %w[text csv json binary].freeze.include?(sql_format)
1538           raise Error, "unsupported COPY TO format: #{format.inspect}"
1539         end
1540         options << "FORMAT #{format}"
1541       end
1542       options << "#{', ' if format}#{opts[:options]}" if opts[:options]
1543       options << ')'
1544     end
1545     table = if table.is_a?(::Sequel::Dataset)
1546       "(#{table.sql})"
1547     else
1548       literal(table)
1549     end
1550     "COPY #{table} TO STDOUT#{options}"
1551   end
1552 end

SQL for doing fast table output to stdout.

create_function_sql (name, definition, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1555       def create_function_sql(name, definition, opts=OPTS)
1556         args = opts[:args]
1557         in_out = %w'OUT INOUT'
1558         if (!opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 && in_out.include?(a[2].to_s)})
1559           returns = opts[:returns] || 'void'
1560         end
1561         language = opts[:language] || 'SQL'
1562         <<-END
1563         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
1564         #{"RETURNS #{returns}" if returns}
1565         LANGUAGE #{language}
1566         #{opts[:behavior].to_s.upcase if opts[:behavior]}
1567         #{'STRICT' if opts[:strict]}
1568         #{'SECURITY DEFINER' if opts[:security_definer]}
1569         #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]}
1570         #{"COST #{opts[:cost]}" if opts[:cost]}
1571         #{"ROWS #{opts[:rows]}" if opts[:rows]}
1572         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
1573         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
1574         END
1575       end

SQL statement to create database function.

create_language_sql (name, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1578 def create_language_sql(name, opts=OPTS)
1579   "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]}"
1580 end

SQL for creating a procedural language.

create_partition_of_table_from_generator (name, generator, options)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1584 def create_partition_of_table_from_generator(name, generator, options)
1585   execute_ddl(create_partition_of_table_sql(name, generator, options))
1586 end

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

create_partition_of_table_sql (name, generator, options)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1589 def create_partition_of_table_sql(name, generator, options)
1590   sql = create_table_prefix_sql(name, options).dup
1591 
1592   sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}"
1593 
1594   case generator.partition_type
1595   when :range
1596     from, to = generator.range
1597     sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}"
1598   when :list
1599     sql << " FOR VALUES IN #{literal(generator.list)}"
1600   when :hash
1601     mod, remainder = generator.hash_values
1602     sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})"
1603   else # when :default
1604     sql << " DEFAULT"
1605   end
1606 
1607   sql << create_table_suffix_sql(name, options)
1608 
1609   sql
1610 end

SQL for creating a partition of another table.

create_schema_sql (name, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1613 def create_schema_sql(name, opts=OPTS)
1614   "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}"
1615 end

SQL for creating a schema.

create_table_as_sql (name, sql, options)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1671 def create_table_as_sql(name, sql, options)
1672   result = create_table_prefix_sql name, options
1673   if on_commit = options[:on_commit]
1674     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
1675   end
1676   result += " AS #{sql}"
1677 end

create_table_generator_class ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1679 def create_table_generator_class
1680   Postgres::CreateTableGenerator
1681 end

create_table_prefix_sql (name, options)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1618 def create_table_prefix_sql(name, options)
1619   prefix_sql = if options[:temp]
1620     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
1621     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
1622     temporary_table_sql
1623   elsif options[:foreign]
1624     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
1625     'FOREIGN '
1626   elsif options.fetch(:unlogged){typecast_value_boolean(@opts[:unlogged_tables_default])}
1627     'UNLOGGED '
1628   end
1629 
1630   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{create_table_table_name_sql(name, options)}"
1631 end

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

create_table_sql (name, generator, options)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1634 def create_table_sql(name, generator, options)
1635   "#{super}#{create_table_suffix_sql(name, options)}"
1636 end

SQL for creating a table with PostgreSQL specific options

create_table_suffix_sql (name, options)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1640 def create_table_suffix_sql(name, options)
1641   sql = String.new
1642 
1643   if inherits = options[:inherits]
1644     sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
1645   end
1646 
1647   if partition_by = options[:partition_by]
1648     sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
1649   end
1650 
1651   if on_commit = options[:on_commit]
1652     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
1653     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
1654     sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
1655   end
1656 
1657   if tablespace = options[:tablespace]
1658     sql << " TABLESPACE #{quote_identifier(tablespace)}"
1659   end
1660 
1661   if server = options[:foreign]
1662     sql << " SERVER #{quote_identifier(server)}"
1663     if foreign_opts = options[:options]
1664       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
1665     end
1666   end
1667 
1668   sql
1669 end

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

create_trigger_sql (table, name, function, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1684 def create_trigger_sql(table, name, function, opts=OPTS)
1685   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
1686   whence = opts[:after] ? 'AFTER' : 'BEFORE'
1687   if filter = opts[:when]
1688     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
1689     filter = " WHEN #{filter_expr(filter)}"
1690   end
1691   "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(', ')})"
1692 end

SQL for creating a database trigger.

create_view_prefix_sql (name, options)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1695 def create_view_prefix_sql(name, options)
1696   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])
1697 
1698   if options[:security_invoker]
1699     sql += " WITH (security_invoker)"
1700   end
1701 
1702   if tablespace = options[:tablespace]
1703     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1704   end
1705 
1706   sql
1707 end

DDL fragment for initial part of CREATE VIEW statement

database_error_regexps ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1506 def database_error_regexps
1507   DATABASE_ERROR_REGEXPS
1508 end

database_specific_error_class_from_sqlstate (sqlstate)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1482 def database_specific_error_class_from_sqlstate(sqlstate)
1483   if sqlstate == '23P01'
1484     ExclusionConstraintViolation
1485   elsif sqlstate == '40P01'
1486     SerializationFailure
1487   elsif sqlstate == '55P03'
1488     DatabaseLockTimeout
1489   else
1490     super
1491   end
1492 end

Calls superclass method

drop_function_sql (name, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1710 def drop_function_sql(name, opts=OPTS)
1711   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
1712 end

SQL for dropping a function from the database.

drop_index_sql (table, op)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1715 def drop_index_sql(table, op)
1716   sch, _ = schema_and_table(table)
1717   "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]}"
1718 end

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

drop_language_sql (name, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1721 def drop_language_sql(name, opts=OPTS)
1722   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1723 end

SQL for dropping a procedural language from the database.

drop_schema_sql (name, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1726 def drop_schema_sql(name, opts=OPTS)
1727   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1728 end

SQL for dropping a schema from the database.

drop_table_sql (name, options)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1736 def drop_table_sql(name, options)
1737   "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
1738 end

Support :foreign tables

drop_trigger_sql (table, name, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1731 def drop_trigger_sql(table, name, opts=OPTS)
1732   "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
1733 end

SQL for dropping a trigger from the database.

drop_view_sql (name, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1741 def drop_view_sql(name, opts=OPTS)
1742   "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
1743 end

SQL for dropping a view from the database.

filter_schema (ds, opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1747 def filter_schema(ds, opts)
1748   expr = if schema = opts[:schema]
1749     if schema.is_a?(SQL::Identifier)
1750       schema.value.to_s
1751     else
1752       schema.to_s
1753     end
1754   else
1755     Sequel.function(:any, Sequel.function(:current_schemas, false))
1756   end
1757   ds.where{{pg_namespace[:nspname]=>expr}}
1758 end

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

index_definition_sql (table_name, index)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1760 def index_definition_sql(table_name, index)
1761   cols = index[:columns]
1762   index_name = index[:name] || default_index_name(table_name, cols)
1763 
1764   expr = if o = index[:opclass]
1765     "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
1766   else
1767     literal(Array(cols))
1768   end
1769 
1770   if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
1771   unique = "UNIQUE " if index[:unique]
1772   index_type = index[:type]
1773   filter = index[:where] || index[:filter]
1774   filter = " WHERE #{filter_expr(filter)}" if filter
1775   nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil?
1776 
1777   case index_type
1778   when :full_text
1779     expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
1780     index_type = index[:index_type] || :gin
1781   when :spatial
1782     index_type = :gist
1783   end
1784 
1785   "CREATE #{unique}INDEX#{' CONCURRENTLY' if index[:concurrently]}#{if_not_exists} #{quote_identifier(index_name)} ON#{' ONLY' if index[:only]} #{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}"
1786 end

initialize_postgres_adapter ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1789 def initialize_postgres_adapter
1790   @primary_keys = {}
1791   @primary_key_sequences = {}
1792   @supported_types = {}
1793   procs = @conversion_procs = CONVERSION_PROCS.dup
1794   procs[1184] = procs[1114] = method(:to_application_timestamp)
1795 end

Setup datastructures shared by all postgres adapters.

pg_class_relname (type, opts) { || ... }

Source

     # File lib/sequel/adapters/shared/postgres.rb
1798 def pg_class_relname(type, opts)
1799   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
1800   ds = filter_schema(ds, opts)
1801   m = output_identifier_meth
1802   if defined?(yield)
1803     yield(ds)
1804   elsif opts[:qualify]
1805     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
1806   else
1807     ds.map{|r| m.call(r[:relname])}
1808   end
1809 end

Backbone of the tables and views support.

regclass_oid (expr, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1813 def regclass_oid(expr, opts=OPTS)
1814   if expr.is_a?(String) && !expr.is_a?(LiteralString)
1815     expr = Sequel.identifier(expr)
1816   end
1817 
1818   sch, table = schema_and_table(expr)
1819   sch ||= opts[:schema]
1820   if sch
1821     expr = Sequel.qualify(sch, table)
1822   end
1823   
1824   expr = if ds = opts[:dataset]
1825     ds.literal(expr)
1826   else
1827     literal(expr)
1828   end
1829 
1830   Sequel.cast(expr.to_s,:regclass).cast(:oid)
1831 end

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

remove_all_cached_schemas ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1844 def remove_all_cached_schemas
1845   @primary_keys.clear
1846   @primary_key_sequences.clear
1847   @schemas.clear
1848 end

Clear all cached schema information

remove_cached_schema (table)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1834 def remove_cached_schema(table)
1835   tab = quote_schema_table(table)
1836   Sequel.synchronize do
1837     @primary_keys.delete(tab)
1838     @primary_key_sequences.delete(tab)
1839   end
1840   super
1841 end

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

Calls superclass method

rename_schema_sql (name, new_name)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1851 def rename_schema_sql(name, new_name)
1852   "ALTER SCHEMA #{quote_identifier(name)} RENAME TO #{quote_identifier(new_name)}"
1853 end

SQL for renaming a schema.

rename_table_sql (name, new_name)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1857 def rename_table_sql(name, new_name)
1858   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
1859 end

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.

schema_array_type (db_type)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1874 def schema_array_type(db_type)
1875   :array
1876 end

The schema :type entry to use for array types.

schema_column_type (db_type)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1862 def schema_column_type(db_type)
1863   case db_type
1864   when /\Ainterval\z/i
1865     :interval
1866   when /\Acitext\z/i
1867     :string
1868   else
1869     super
1870   end
1871 end

Handle interval and citext types.

Calls superclass method

schema_composite_type (db_type)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1879 def schema_composite_type(db_type)
1880   :composite
1881 end

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

schema_enum_type (db_type)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1884 def schema_enum_type(db_type)
1885   :enum
1886 end

The schema :type entry to use for enum types.

schema_multirange_type (db_type)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1894 def schema_multirange_type(db_type)
1895   :multirange
1896 end

The schema :type entry to use for multirange types.

schema_parse_table (table_name, opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1911 def schema_parse_table(table_name, opts)
1912   m = output_identifier_meth(opts[:dataset])
1913 
1914   _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row|
1915     row[:default] = nil if blank_object?(row[:default])
1916     if row[:base_oid]
1917       row[:domain_oid] = row[:oid]
1918       row[:oid] = row.delete(:base_oid)
1919       row[:db_domain_type] = row[:db_type]
1920       row[:db_type] = row.delete(:db_base_type)
1921     else
1922       row.delete(:base_oid)
1923       row.delete(:db_base_type)
1924     end
1925 
1926     db_type = row[:db_type]
1927     row[:type] = if row.delete(:is_array)
1928       schema_array_type(db_type)
1929     else
1930       send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type)
1931     end
1932     identity = row.delete(:attidentity)
1933     if row[:primary_key]
1934       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
1935     end
1936 
1937     # simplecov:disable
1938     if server_version >= 90600
1939     # simplecov:enable
1940       case row[:oid]
1941       when 1082
1942         row[:min_value] = MIN_DATE
1943         row[:max_value] = MAX_DATE
1944       when 1184, 1114
1945         if Sequel.datetime_class == Time
1946           row[:min_value] = MIN_TIMESTAMP
1947           row[:max_value] = MAX_TIMESTAMP
1948         end
1949       end
1950     end
1951 
1952     [m.call(row.delete(:name)), row]
1953   end
1954 end

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

schema_range_type (db_type)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1889 def schema_range_type(db_type)
1890   :range
1891 end

The schema :type entry to use for range types.

set_transaction_isolation (conn, opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1957 def set_transaction_isolation(conn, opts)
1958   level = opts.fetch(:isolation, transaction_isolation_level)
1959   read_only = opts[:read_only]
1960   deferrable = opts[:deferrable]
1961   if level || !read_only.nil? || !deferrable.nil?
1962     sql = String.new
1963     sql << "SET TRANSACTION"
1964     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
1965     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
1966     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
1967     log_connection_execute(conn, sql)
1968   end
1969 end

Set the transaction isolation level on the given connection

sql_function_args (args)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1972 def sql_function_args(args)
1973   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
1974 end

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

supports_combining_alter_table_ops? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1977 def supports_combining_alter_table_ops?
1978   true
1979 end

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

supports_create_or_replace_view? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
1982 def supports_create_or_replace_view?
1983   true
1984 end

PostgreSQL supports CREATE OR REPLACE VIEW.

type_literal_generic_bignum_symbol (column)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1987 def type_literal_generic_bignum_symbol(column)
1988   column[:serial] ? :bigserial : super
1989 end

Handle bigserial type if :serial option is present

Calls superclass method

type_literal_generic_file (column)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1992 def type_literal_generic_file(column)
1993   :bytea
1994 end

PostgreSQL uses the bytea data type for blobs

type_literal_generic_integer (column)

Source

     # File lib/sequel/adapters/shared/postgres.rb
1997 def type_literal_generic_integer(column)
1998   column[:serial] ? :serial : super
1999 end

Handle serial type if :serial option is present

Calls superclass method

type_literal_generic_string (column)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2005 def type_literal_generic_string(column)
2006   if column[:text]
2007     :text
2008   elsif column[:fixed]
2009     "char(#{column[:size]||default_string_column_size})"
2010   elsif column[:text] == false || column[:size]
2011     "varchar(#{column[:size]||default_string_column_size})"
2012   else
2013     :text
2014   end
2015 end

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.

unique_constraint_sql_fragment (constraint)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2018 def unique_constraint_sql_fragment(constraint)
2019   if constraint[:nulls_not_distinct]
2020     'UNIQUE NULLS NOT DISTINCT'
2021   else
2022     'UNIQUE'
2023   end
2024 end

Support :nulls_not_distinct option.

view_with_check_option_support ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2027 def view_with_check_option_support
2028   # simplecov:disable
2029   :local if server_version >= 90400
2030   # simplecov:enable
2031 end

PostgreSQL 9.4+ supports views with check option.