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
327 def add_conversion_proc(oid, callable=nil, &block)
328   conversion_procs[oid] = callable || block
329 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
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

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
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

commit_prepared_transaction(transaction_id, opts=OPTS) click to toggle source

    # File lib/sequel/adapters/shared/postgres.rb
341 def commit_prepared_transaction(transaction_id, opts=OPTS)
342   run("COMMIT PREPARED #{literal(transaction_id)}", opts)
343 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
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", 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

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
455 def create_function(name, definition, opts=OPTS)
456   self << create_function_sql(name, definition, opts)
457 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
466 def create_language(name, opts=OPTS)
467   self << create_language_sql(name, opts)
468 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
475 def create_schema(name, opts=OPTS)
476   self << create_schema_sql(name, opts)
477 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
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

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
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

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
511 def create_trigger(table, name, function, opts=OPTS)
512   self << create_trigger_sql(table, name, function, opts)
513 end

database_type() click to toggle source

    # File lib/sequel/adapters/shared/postgres.rb
515 def database_type
516   :postgres
517 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
534 def defer_constraints(opts=OPTS)
535   _set_constraints(' DEFERRED', opts)
536 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
543 def do(code, opts=OPTS)
544   language = opts[:language]
545   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}"
546 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
554 def drop_function(name, opts=OPTS)
555   self << drop_function_sql(name, opts)
556 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
563 def drop_language(name, opts=OPTS)
564   self << drop_language_sql(name, opts)
565 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
572 def drop_schema(name, opts=OPTS)
573   self << drop_schema_sql(name, opts)
574   remove_all_cached_schemas
575 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
583 def drop_trigger(table, name, opts=OPTS)
584   self << drop_trigger_sql(table, name, opts)
585 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
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

freeze() click to toggle source

Calls superclass method

    # 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

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
668 def immediate_constraints(opts=OPTS)
669   _set_constraints(' IMMEDIATE', opts)
670 end

indexes(table, opts=OPTS) click to toggle source

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.

    # 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

locks() click to toggle source

Dataset containing all current database locks

    # 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

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
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

primary_key(table, opts=OPTS) click to toggle source

Return primary key for the given table.

    # 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

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
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

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
758 def refresh_view(name, opts=OPTS)
759   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
760 end

rename_schema(name, new_name) click to toggle source

Rename a schema in the database. Arguments:

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

    # File lib/sequel/adapters/shared/postgres.rb
747 def rename_schema(name, new_name)
748   self << rename_schema_sql(name, new_name)
749   remove_all_cached_schemas
750 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
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)))
773     increment_by = :seqincrement
774     min_value = :seqmin
775   # :nocov:
776   else
777     seq_ds = metadata_dataset.from(LiteralString.new(seq))
778     increment_by = :increment_by
779     min_value = :min_value
780   # :nocov:
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

rollback_prepared_transaction(transaction_id, opts=OPTS) click to toggle source

    # File lib/sequel/adapters/shared/postgres.rb
786 def rollback_prepared_transaction(transaction_id, opts=OPTS)
787   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
788 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
792 def serial_primary_key_options
793   # :nocov:
794   auto_increment_key = server_version >= 100002 ? :identity : :serial
795   # :nocov:
796   {:primary_key => true, auto_increment_key => true, :type=>Integer}
797 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
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

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
808 def supports_create_table_if_not_exists?
809   server_version >= 90100
810 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
813 def supports_deferrable_constraints?
814   server_version >= 90000
815 end

supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

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

supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

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

supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

    # File lib/sequel/adapters/shared/postgres.rb
828 def supports_partial_indexes?
829   true
830 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
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

supports_savepoints?() click to toggle source

PostgreSQL supports savepoints

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

supports_transaction_isolation_levels?() click to toggle source

PostgreSQL supports transaction isolation levels

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

supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

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

supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
833 def supports_trigger_conditions?
834   server_version >= 90000
835 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
868 def tables(opts=OPTS, &block)
869   pg_class_relname(['r', 'p'], opts, &block)
870 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
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

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
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

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
900 def views(opts=OPTS)
901   relkind = opts[:materialized] ? 'm' : 'v'
902   pg_class_relname(relkind, opts)
903 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
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

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
 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   # :nocov:
 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   # :nocov:
 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

_add_validated_enforced_constraint_columns(ds) click to toggle 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   # :nocov:
1025   else
1026     Sequel.cast(true, TrueClass)
1027   # :nocov:
1028   end
1029   ds = ds.select_append(validated_cond.as(:validated))
1030 
1031   enforced_cond = if server_version >= 180000
1032     Sequel[:conenforced]
1033   # :nocov:
1034   else
1035     Sequel.cast(true, TrueClass)
1036   # :nocov:
1037   end
1038   ds = ds.select_append(enforced_cond.as(:enforced))
1039 
1040   ds
1041 end

_check_constraints_ds() click to toggle source

Dataset used to retrieve CHECK constraint information

    # 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

_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
956 def _foreign_key_list_ds
957   @_foreign_key_list_ds ||= __foreign_key_list_ds(false)
958 end

_indexes_ds() click to toggle source

Dataset used to retrieve index information

     # 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     # :nocov:
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     # :nocov:
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     # :nocov:
1071     ds = ds.where(:indisready=>true) if server_version >= 80300
1072     ds = ds.where(:indislive=>true) if server_version >= 90300
1073     # :nocov:
1074 
1075     ds
1076   end
1077 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
961 def _reverse_foreign_key_list_ds
962   @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true)
963 end

_schema_ds() click to toggle source

Dataset used to get schema for tables

     # 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     # :nocov:
1167     if server_version > 100000
1168     # :nocov:
1169       ds = ds.select_append{pg_attribute[:attidentity]}
1170 
1171       # :nocov:
1172       if server_version > 120000
1173       # :nocov:
1174         ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)}
1175       end
1176     end
1177 
1178     ds
1179   end
1180 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
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

_select_pk_ds() click to toggle source

Dataset used to determine primary keys for tables

     # 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

_select_serial_sequence_ds() click to toggle source

Dataset used to determine normal serial sequences for tables

     # 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

_set_constraints(type, opts) click to toggle source

Internals of defer_constraints/immediate_constraints

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

_set_constraints_sql(type, opts) click to toggle source

SQL to use for SET CONSTRAINTS

     # 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

_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
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

alter_table_add_column_sql(table, op) click to toggle 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) click to toggle 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) click to toggle 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() click to toggle 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) click to toggle 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) click to toggle source

Calls superclass method

     # 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

alter_table_validate_constraint_sql(table, op) click to toggle 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) 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
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     end
1276 
1277     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
1278   end
1279 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
1282 def begin_savepoint(conn, opts)
1283   super
1284 
1285   unless (read_only = opts[:read_only]).nil?
1286     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
1287   end
1288 end

column_definition_add_references_sql(sql, column) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1436 def column_definition_add_references_sql(sql, column)
1437   super
1438   if column[:not_enforced]
1439     sql << " NOT ENFORCED"
1440   end
1441 end

column_definition_append_include_sql(sql, constraint) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1290 def column_definition_append_include_sql(sql, constraint)
1291   if include_cols = constraint[:include]
1292     sql << " INCLUDE " << literal(Array(include_cols))
1293   end
1294 end

column_definition_append_primary_key_sql(sql, constraint) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1296 def column_definition_append_primary_key_sql(sql, constraint)
1297   super
1298   column_definition_append_include_sql(sql, constraint)
1299 end

column_definition_append_unique_sql(sql, constraint) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1301 def column_definition_append_unique_sql(sql, constraint)
1302   super
1303   column_definition_append_include_sql(sql, constraint)
1304 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
1308 def column_definition_collate_sql(sql, column)
1309   if collate = column[:collate]
1310     collate = literal(collate) unless collate.is_a?(String)
1311     sql << " COLLATE #{collate}"
1312   end
1313 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
1317 def column_definition_default_sql(sql, column)
1318   super
1319   if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
1320     if (identity = column[:identity])
1321       sql << " GENERATED "
1322       sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
1323       sql << " AS IDENTITY"
1324     elsif (generated = column[:generated_always_as])
1325       sql << " GENERATED ALWAYS AS (#{literal(generated)}) #{column[:virtual] ? 'VIRTUAL' : 'STORED'}"
1326     end
1327   end
1328 end

column_definition_null_sql(sql, column) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1443 def column_definition_null_sql(sql, column)
1444   constraint = column[:not_null]
1445   constraint = nil unless constraint.is_a?(Hash)
1446   if constraint && (name = constraint[:name])
1447     sql << " CONSTRAINT #{quote_identifier(name)}"
1448   end
1449   super
1450   if constraint && constraint[:no_inherit]
1451     sql << " NO INHERIT"
1452   end
1453 end

column_references_add_period(cols) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1470 def column_references_add_period(cols)
1471   cols= cols.dup
1472   cols[-1] = Sequel.lit("PERIOD #{quote_identifier(cols[-1])}")
1473   cols
1474 end

column_references_append_key_sql(sql, column) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1464 def column_references_append_key_sql(sql, column)
1465   cols = Array(column[:key])
1466   cols = column_references_add_period(cols) if column[:period]
1467   sql << "(#{cols.map{|x| quote_identifier(x)}.join(', ')})"
1468 end

column_references_table_constraint_sql(constraint) click to toggle source

Handle :period option

     # File lib/sequel/adapters/shared/postgres.rb
1456 def column_references_table_constraint_sql(constraint)
1457   sql = String.new
1458   sql << "FOREIGN KEY "
1459   cols = constraint[:columns]
1460   cols = column_references_add_period(cols) if constraint[:period]
1461   sql << literal(cols) << column_references_sql(constraint)
1462 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
1331 def column_schema_normalize_default(default, type)
1332   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
1333     default = m[1] || m[2]
1334   end
1335   super(default, type)
1336 end

combinable_alter_table_op?(op) click to toggle source

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

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1350 def combinable_alter_table_op?(op)
1351   (super || op[:op] == :validate_constraint || op[:op] == :alter_constraint) && op[:op] != :rename_column
1352 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
1340 def commit_transaction(conn, opts=OPTS)
1341   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
1342     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
1343   else
1344     super
1345   end
1346 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
1356 def connection_configuration_sqls(opts=@opts)
1357   sqls = []
1358 
1359   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
1360 
1361   cmm = opts.fetch(:client_min_messages, :warning)
1362   if cmm && !cmm.to_s.empty?
1363     cmm = cmm.to_s.upcase.strip
1364     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
1365       raise Error, "Unsupported client_min_messages setting: #{cmm}"
1366     end
1367     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
1368   end
1369 
1370   if search_path = opts[:search_path]
1371     case search_path
1372     when String
1373       search_path = search_path.split(",").map(&:strip)
1374     when Array
1375       # nil
1376     else
1377       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
1378     end
1379     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
1380   end
1381 
1382   sqls
1383 end

constraint_definition_sql(constraint) click to toggle source

Handle PostgreSQL-specific constraint features.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1386 def constraint_definition_sql(constraint)
1387   case type = constraint[:type]
1388   when :exclude
1389     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
1390     sql = String.new
1391     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
1392     sql << "EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})"
1393     column_definition_append_include_sql(sql, constraint)
1394     sql << " WHERE #{filter_expr(constraint[:where])}" if constraint[:where]
1395     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1396     sql
1397   when :primary_key, :unique
1398     sql = String.new
1399     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
1400 
1401     if type == :primary_key
1402       sql << primary_key_constraint_sql_fragment(constraint)
1403     else
1404       sql << unique_constraint_sql_fragment(constraint)
1405     end
1406 
1407     if using_index = constraint[:using_index]
1408       sql << " USING INDEX " << quote_identifier(using_index)
1409     else
1410       cols = literal(constraint[:columns])
1411       cols.insert(-2, " WITHOUT OVERLAPS") if constraint[:without_overlaps]
1412       sql << " " << cols
1413 
1414       if include_cols = constraint[:include]
1415         sql << " INCLUDE " << literal(Array(include_cols))
1416       end
1417     end
1418 
1419     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1420     sql
1421   else # when :foreign_key, :check
1422     sql = super
1423     if constraint[:no_inherit]
1424       sql << " NO INHERIT"
1425     end
1426     if constraint[:not_enforced]
1427       sql << " NOT ENFORCED"
1428     end
1429     if constraint[:not_valid]
1430       sql << " NOT VALID"
1431     end
1432     sql
1433   end
1434 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
1505 def copy_into_sql(table, opts)
1506   sql = String.new
1507   sql << "COPY #{literal(table)}"
1508   if cols = opts[:columns]
1509     sql << literal(Array(cols))
1510   end
1511   sql << " FROM STDIN"
1512   if opts[:options] || opts[:format]
1513     sql << " ("
1514     sql << "FORMAT #{opts[:format]}" if opts[:format]
1515     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1516     sql << ')'
1517   end
1518   sql
1519 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
1522 def copy_table_sql(table, opts)
1523   if table.is_a?(String)
1524     table
1525   else
1526     if opts[:options] || opts[:format]
1527       options = String.new
1528       options << " ("
1529       options << "FORMAT #{opts[:format]}" if opts[:format]
1530       options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1531       options << ')'
1532     end
1533     table = if table.is_a?(::Sequel::Dataset)
1534       "(#{table.sql})"
1535     else
1536       literal(table)
1537     end
1538     "COPY #{table} TO STDOUT#{options}"
1539   end
1540 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
1543       def create_function_sql(name, definition, opts=OPTS)
1544         args = opts[:args]
1545         in_out = %w'OUT INOUT'
1546         if (!opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 && in_out.include?(a[2].to_s)})
1547           returns = opts[:returns] || 'void'
1548         end
1549         language = opts[:language] || 'SQL'
1550         <<-END
1551         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
1552         #{"RETURNS #{returns}" if returns}
1553         LANGUAGE #{language}
1554         #{opts[:behavior].to_s.upcase if opts[:behavior]}
1555         #{'STRICT' if opts[:strict]}
1556         #{'SECURITY DEFINER' if opts[:security_definer]}
1557         #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]}
1558         #{"COST #{opts[:cost]}" if opts[:cost]}
1559         #{"ROWS #{opts[:rows]}" if opts[:rows]}
1560         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
1561         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
1562         END
1563       end

create_language_sql(name, opts=OPTS) click to toggle source

SQL for creating a procedural language.

     # File lib/sequel/adapters/shared/postgres.rb
1566 def create_language_sql(name, opts=OPTS)
1567   "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]}"
1568 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
1572 def create_partition_of_table_from_generator(name, generator, options)
1573   execute_ddl(create_partition_of_table_sql(name, generator, options))
1574 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
1577 def create_partition_of_table_sql(name, generator, options)
1578   sql = create_table_prefix_sql(name, options).dup
1579 
1580   sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}"
1581 
1582   case generator.partition_type
1583   when :range
1584     from, to = generator.range
1585     sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}"
1586   when :list
1587     sql << " FOR VALUES IN #{literal(generator.list)}"
1588   when :hash
1589     mod, remainder = generator.hash_values
1590     sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})"
1591   else # when :default
1592     sql << " DEFAULT"
1593   end
1594 
1595   sql << create_table_suffix_sql(name, options)
1596 
1597   sql
1598 end

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

SQL for creating a schema.

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

create_table_as_sql(name, sql, options) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1659 def create_table_as_sql(name, sql, options)
1660   result = create_table_prefix_sql name, options
1661   if on_commit = options[:on_commit]
1662     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
1663   end
1664   result += " AS #{sql}"
1665 end

create_table_generator_class() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1667 def create_table_generator_class
1668   Postgres::CreateTableGenerator
1669 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
1606 def create_table_prefix_sql(name, options)
1607   prefix_sql = if options[:temp]
1608     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
1609     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
1610     temporary_table_sql
1611   elsif options[:foreign]
1612     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
1613     'FOREIGN '
1614   elsif options.fetch(:unlogged){typecast_value_boolean(@opts[:unlogged_tables_default])}
1615     'UNLOGGED '
1616   end
1617 
1618   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{create_table_table_name_sql(name, options)}"
1619 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
1622 def create_table_sql(name, generator, options)
1623   "#{super}#{create_table_suffix_sql(name, options)}"
1624 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
1628 def create_table_suffix_sql(name, options)
1629   sql = String.new
1630 
1631   if inherits = options[:inherits]
1632     sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
1633   end
1634 
1635   if partition_by = options[:partition_by]
1636     sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
1637   end
1638 
1639   if on_commit = options[:on_commit]
1640     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
1641     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
1642     sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
1643   end
1644 
1645   if tablespace = options[:tablespace]
1646     sql << " TABLESPACE #{quote_identifier(tablespace)}"
1647   end
1648 
1649   if server = options[:foreign]
1650     sql << " SERVER #{quote_identifier(server)}"
1651     if foreign_opts = options[:options]
1652       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
1653     end
1654   end
1655 
1656   sql
1657 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
1672 def create_trigger_sql(table, name, function, opts=OPTS)
1673   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
1674   whence = opts[:after] ? 'AFTER' : 'BEFORE'
1675   if filter = opts[:when]
1676     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
1677     filter = " WHEN #{filter_expr(filter)}"
1678   end
1679   "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(', ')})"
1680 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
1683 def create_view_prefix_sql(name, options)
1684   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])
1685 
1686   if options[:security_invoker]
1687     sql += " WITH (security_invoker)"
1688   end
1689 
1690   if tablespace = options[:tablespace]
1691     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1692   end
1693 
1694   sql
1695 end

database_error_regexps() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1500 def database_error_regexps
1501   DATABASE_ERROR_REGEXPS
1502 end

database_specific_error_class_from_sqlstate(sqlstate) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1476 def database_specific_error_class_from_sqlstate(sqlstate)
1477   if sqlstate == '23P01'
1478     ExclusionConstraintViolation
1479   elsif sqlstate == '40P01'
1480     SerializationFailure
1481   elsif sqlstate == '55P03'
1482     DatabaseLockTimeout
1483   else
1484     super
1485   end
1486 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
1698 def drop_function_sql(name, opts=OPTS)
1699   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
1700 end

drop_index_sql(table, op) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1703 def drop_index_sql(table, op)
1704   sch, _ = schema_and_table(table)
1705   "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]}"
1706 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
1709 def drop_language_sql(name, opts=OPTS)
1710   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1711 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
1714 def drop_schema_sql(name, opts=OPTS)
1715   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1716 end

drop_table_sql(name, options) click to toggle source

Support :foreign tables

     # File lib/sequel/adapters/shared/postgres.rb
1724 def drop_table_sql(name, options)
1725   "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
1726 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
1719 def drop_trigger_sql(table, name, opts=OPTS)
1720   "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
1721 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
1729 def drop_view_sql(name, opts=OPTS)
1730   "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
1731 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
1735 def filter_schema(ds, opts)
1736   expr = if schema = opts[:schema]
1737     if schema.is_a?(SQL::Identifier)
1738       schema.value.to_s
1739     else
1740       schema.to_s
1741     end
1742   else
1743     Sequel.function(:any, Sequel.function(:current_schemas, false))
1744   end
1745   ds.where{{pg_namespace[:nspname]=>expr}}
1746 end

index_definition_sql(table_name, index) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1748 def index_definition_sql(table_name, index)
1749   cols = index[:columns]
1750   index_name = index[:name] || default_index_name(table_name, cols)
1751 
1752   expr = if o = index[:opclass]
1753     "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
1754   else
1755     literal(Array(cols))
1756   end
1757 
1758   if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
1759   unique = "UNIQUE " if index[:unique]
1760   index_type = index[:type]
1761   filter = index[:where] || index[:filter]
1762   filter = " WHERE #{filter_expr(filter)}" if filter
1763   nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil?
1764 
1765   case index_type
1766   when :full_text
1767     expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
1768     index_type = index[:index_type] || :gin
1769   when :spatial
1770     index_type = :gist
1771   end
1772 
1773   "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}"
1774 end

initialize_postgres_adapter() click to toggle source

Setup datastructures shared by all postgres adapters.

     # File lib/sequel/adapters/shared/postgres.rb
1777 def initialize_postgres_adapter
1778   @primary_keys = {}
1779   @primary_key_sequences = {}
1780   @supported_types = {}
1781   procs = @conversion_procs = CONVERSION_PROCS.dup
1782   procs[1184] = procs[1114] = method(:to_application_timestamp)
1783 end

pg_class_relname(type, opts) { || ... } click to toggle source

Backbone of the tables and views support.

     # File lib/sequel/adapters/shared/postgres.rb
1786 def pg_class_relname(type, opts)
1787   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
1788   ds = filter_schema(ds, opts)
1789   m = output_identifier_meth
1790   if defined?(yield)
1791     yield(ds)
1792   elsif opts[:qualify]
1793     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
1794   else
1795     ds.map{|r| m.call(r[:relname])}
1796   end
1797 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
1801 def regclass_oid(expr, opts=OPTS)
1802   if expr.is_a?(String) && !expr.is_a?(LiteralString)
1803     expr = Sequel.identifier(expr)
1804   end
1805 
1806   sch, table = schema_and_table(expr)
1807   sch ||= opts[:schema]
1808   if sch
1809     expr = Sequel.qualify(sch, table)
1810   end
1811   
1812   expr = if ds = opts[:dataset]
1813     ds.literal(expr)
1814   else
1815     literal(expr)
1816   end
1817 
1818   Sequel.cast(expr.to_s,:regclass).cast(:oid)
1819 end

remove_all_cached_schemas() click to toggle source

Clear all cached schema information

     # File lib/sequel/adapters/shared/postgres.rb
1832 def remove_all_cached_schemas
1833   @primary_keys.clear
1834   @primary_key_sequences.clear
1835   @schemas.clear
1836 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
1822 def remove_cached_schema(table)
1823   tab = quote_schema_table(table)
1824   Sequel.synchronize do
1825     @primary_keys.delete(tab)
1826     @primary_key_sequences.delete(tab)
1827   end
1828   super
1829 end

rename_schema_sql(name, new_name) click to toggle source

SQL for renaming a schema.

     # File lib/sequel/adapters/shared/postgres.rb
1839 def rename_schema_sql(name, new_name)
1840   "ALTER SCHEMA #{quote_identifier(name)} RENAME TO #{quote_identifier(new_name)}"
1841 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
1845 def rename_table_sql(name, new_name)
1846   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
1847 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
1862 def schema_array_type(db_type)
1863   :array
1864 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
1850 def schema_column_type(db_type)
1851   case db_type
1852   when /\Ainterval\z/i
1853     :interval
1854   when /\Acitext\z/i
1855     :string
1856   else
1857     super
1858   end
1859 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
1867 def schema_composite_type(db_type)
1868   :composite
1869 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
1872 def schema_enum_type(db_type)
1873   :enum
1874 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
1882 def schema_multirange_type(db_type)
1883   :multirange
1884 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
1899 def schema_parse_table(table_name, opts)
1900   m = output_identifier_meth(opts[:dataset])
1901 
1902   _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row|
1903     row[:default] = nil if blank_object?(row[:default])
1904     if row[:base_oid]
1905       row[:domain_oid] = row[:oid]
1906       row[:oid] = row.delete(:base_oid)
1907       row[:db_domain_type] = row[:db_type]
1908       row[:db_type] = row.delete(:db_base_type)
1909     else
1910       row.delete(:base_oid)
1911       row.delete(:db_base_type)
1912     end
1913 
1914     db_type = row[:db_type]
1915     row[:type] = if row.delete(:is_array)
1916       schema_array_type(db_type)
1917     else
1918       send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type)
1919     end
1920     identity = row.delete(:attidentity)
1921     if row[:primary_key]
1922       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
1923     end
1924 
1925     # :nocov:
1926     if server_version >= 90600
1927     # :nocov:
1928       case row[:oid]
1929       when 1082
1930         row[:min_value] = MIN_DATE
1931         row[:max_value] = MAX_DATE
1932       when 1184, 1114
1933         if Sequel.datetime_class == Time
1934           row[:min_value] = MIN_TIMESTAMP
1935           row[:max_value] = MAX_TIMESTAMP
1936         end
1937       end
1938     end
1939 
1940     [m.call(row.delete(:name)), row]
1941   end
1942 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
1877 def schema_range_type(db_type)
1878   :range
1879 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
1945 def set_transaction_isolation(conn, opts)
1946   level = opts.fetch(:isolation, transaction_isolation_level)
1947   read_only = opts[:read_only]
1948   deferrable = opts[:deferrable]
1949   if level || !read_only.nil? || !deferrable.nil?
1950     sql = String.new
1951     sql << "SET TRANSACTION"
1952     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
1953     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
1954     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
1955     log_connection_execute(conn, sql)
1956   end
1957 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
1960 def sql_function_args(args)
1961   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
1962 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
1965 def supports_combining_alter_table_ops?
1966   true
1967 end

supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1970 def supports_create_or_replace_view?
1971   true
1972 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
1975 def type_literal_generic_bignum_symbol(column)
1976   column[:serial] ? :bigserial : super
1977 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
1980 def type_literal_generic_file(column)
1981   :bytea
1982 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
1985 def type_literal_generic_integer(column)
1986   column[:serial] ? :serial : super
1987 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
1993 def type_literal_generic_string(column)
1994   if column[:text]
1995     :text
1996   elsif column[:fixed]
1997     "char(#{column[:size]||default_string_column_size})"
1998   elsif column[:text] == false || column[:size]
1999     "varchar(#{column[:size]||default_string_column_size})"
2000   else
2001     :text
2002   end
2003 end

unique_constraint_sql_fragment(constraint) click to toggle source

Support :nulls_not_distinct option.

     # File lib/sequel/adapters/shared/postgres.rb
2006 def unique_constraint_sql_fragment(constraint)
2007   if constraint[:nulls_not_distinct]
2008     'UNIQUE NULLS NOT DISTINCT'
2009   else
2010     'UNIQUE'
2011   end
2012 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
2015 def view_with_check_option_support
2016   # :nocov:
2017   :local if server_version >= 90400
2018   # :nocov:
2019 end