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

    # File lib/sequel/adapters/shared/postgres.rb
673 def indexes(table, opts=OPTS)
674   m = output_identifier_meth
675   cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)}
676   cond[:indpred] = nil unless opts[:include_partial]
677 
678   indexes = {}
679   _indexes_ds.where_each(cond) do |r|
680     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
681     i[:columns] << m.call(r[:column])
682   end
683   indexes
684 end

locks() click to toggle source

Dataset containing all current database locks

    # File lib/sequel/adapters/shared/postgres.rb
687 def locks
688   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
689 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
697 def notify(channel, opts=OPTS)
698   sql = String.new
699   sql << "NOTIFY "
700   dataset.send(:identifier_append, sql, channel)
701   if payload = opts[:payload]
702     sql << ", "
703     dataset.literal_append(sql, payload.to_s)
704   end
705   execute_ddl(sql, opts)
706 end

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

Return primary key for the given table.

    # File lib/sequel/adapters/shared/postgres.rb
709 def primary_key(table, opts=OPTS)
710   quoted_table = quote_schema_table(table)
711   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
712   value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts))
713   Sequel.synchronize{@primary_keys[quoted_table] = value}
714 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
717 def primary_key_sequence(table, opts=OPTS)
718   quoted_table = quote_schema_table(table)
719   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
720   cond = {Sequel[:t][:oid] => regclass_oid(table, opts)}
721   value = if pks = _select_serial_sequence_ds.first(cond)
722     literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
723   elsif pks = _select_custom_sequence_ds.first(cond)
724     literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
725   end
726 
727   Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value
728 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
744 def refresh_view(name, opts=OPTS)
745   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
746 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
733 def rename_schema(name, new_name)
734   self << rename_schema_sql(name, new_name)
735   remove_all_cached_schemas
736 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
750 def reset_primary_key_sequence(table)
751   return unless seq = primary_key_sequence(table)
752   pk = SQL::Identifier.new(primary_key(table))
753   db = self
754   s, t = schema_and_table(table)
755   table = Sequel.qualify(s, t) if s
756 
757   if server_version >= 100000
758     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq)))
759     increment_by = :seqincrement
760     min_value = :seqmin
761   # :nocov:
762   else
763     seq_ds = metadata_dataset.from(LiteralString.new(seq))
764     increment_by = :increment_by
765     min_value = :min_value
766   # :nocov:
767   end
768 
769   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
770 end

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

    # File lib/sequel/adapters/shared/postgres.rb
772 def rollback_prepared_transaction(transaction_id, opts=OPTS)
773   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
774 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
778 def serial_primary_key_options
779   # :nocov:
780   auto_increment_key = server_version >= 100002 ? :identity : :serial
781   # :nocov:
782   {:primary_key => true, auto_increment_key => true, :type=>Integer}
783 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
786 def server_version(server=nil)
787   return @server_version if @server_version
788   ds = dataset
789   ds = ds.server(server) if server
790   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
791 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
794 def supports_create_table_if_not_exists?
795   server_version >= 90100
796 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
799 def supports_deferrable_constraints?
800   server_version >= 90000
801 end

supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

    # File lib/sequel/adapters/shared/postgres.rb
804 def supports_deferrable_foreign_key_constraints?
805   true
806 end

supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

    # File lib/sequel/adapters/shared/postgres.rb
809 def supports_drop_table_if_exists?
810   true
811 end

supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

    # File lib/sequel/adapters/shared/postgres.rb
814 def supports_partial_indexes?
815   true
816 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
825 def supports_prepared_transactions?
826   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
827   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
828 end

supports_savepoints?() click to toggle source

PostgreSQL supports savepoints

    # File lib/sequel/adapters/shared/postgres.rb
831 def supports_savepoints?
832   true
833 end

supports_transaction_isolation_levels?() click to toggle source

PostgreSQL supports transaction isolation levels

    # File lib/sequel/adapters/shared/postgres.rb
836 def supports_transaction_isolation_levels?
837   true
838 end

supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

    # File lib/sequel/adapters/shared/postgres.rb
841 def supports_transactional_ddl?
842   true
843 end

supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
819 def supports_trigger_conditions?
820   server_version >= 90000
821 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
854 def tables(opts=OPTS, &block)
855   pg_class_relname(['r', 'p'], opts, &block)
856 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
860 def type_supported?(type)
861   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
862   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
863   Sequel.synchronize{return @supported_types[type] = supported}
864 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
873 def values(v)
874   raise Error, "Cannot provide an empty array for values" if v.empty?
875   @default_dataset.clone(:values=>v)
876 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
886 def views(opts=OPTS)
887   relkind = opts[:materialized] ? 'm' : 'v'
888   pg_class_relname(relkind, opts)
889 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
902 def with_advisory_lock(lock_id, opts=OPTS)
903   ds = dataset
904   if server = opts[:server]
905     ds = ds.server(server)
906   end
907 
908   synchronize(server) do |c|
909     begin
910       if opts[:wait]
911         ds.get{pg_advisory_lock(lock_id)}
912         locked = true
913       else
914         unless locked = ds.get{pg_try_advisory_lock(lock_id)}
915           raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}"
916         end
917       end
918 
919       yield
920     ensure
921       ds.get{pg_advisory_unlock(lock_id)} if locked
922     end
923   end
924 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
 952 def __foreign_key_list_ds(reverse)
 953   if reverse
 954     ctable = Sequel[:att2]
 955     cclass = Sequel[:cl2]
 956     rtable = Sequel[:att]
 957     rclass = Sequel[:cl]
 958   else
 959     ctable = Sequel[:att]
 960     cclass = Sequel[:cl]
 961     rtable = Sequel[:att2]
 962     rclass = Sequel[:cl2]
 963   end
 964 
 965   if server_version >= 90500
 966     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
 967     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
 968   # :nocov:
 969   else
 970     range = 0...32
 971     cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
 972     rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
 973   # :nocov:
 974   end
 975 
 976   ds = metadata_dataset.
 977     from{pg_constraint.as(:co)}.
 978     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
 979     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
 980     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
 981     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
 982     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
 983     order{[co[:conname], cpos]}.
 984     where{{
 985       cl[:relkind]=>%w'r p',
 986       co[:contype]=>'f',
 987       cpos=>rpos
 988     }}.
 989     select{[
 990       co[:conname].as(:name),
 991       ctable[:attname].as(:column),
 992       co[:confupdtype].as(:on_update),
 993       co[:confdeltype].as(:on_delete),
 994       cl2[:relname].as(:table),
 995       rtable[:attname].as(:refcolumn),
 996       SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
 997       nsp[:nspname].as(:schema)
 998     ]}
 999 
1000   if reverse
1001     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
1002   end
1003 
1004   _add_validated_enforced_constraint_columns(ds)
1005 end

_add_validated_enforced_constraint_columns(ds) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1007 def _add_validated_enforced_constraint_columns(ds)
1008   validated_cond = if server_version >= 90100
1009     Sequel[:convalidated]
1010   # :nocov:
1011   else
1012     Sequel.cast(true, TrueClass)
1013   # :nocov:
1014   end
1015   ds = ds.select_append(validated_cond.as(:validated))
1016 
1017   enforced_cond = if server_version >= 180000
1018     Sequel[:conenforced]
1019   # :nocov:
1020   else
1021     Sequel.cast(true, TrueClass)
1022   # :nocov:
1023   end
1024   ds = ds.select_append(enforced_cond.as(:enforced))
1025 
1026   ds
1027 end

_check_constraints_ds() click to toggle source

Dataset used to retrieve CHECK constraint information

    # File lib/sequel/adapters/shared/postgres.rb
929 def _check_constraints_ds
930   @_check_constraints_ds ||= begin
931     ds = metadata_dataset.
932       from{pg_constraint.as(:co)}.
933       left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
934       where(:contype=>'c').
935       select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
936 
937     _add_validated_enforced_constraint_columns(ds)
938   end
939 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
942 def _foreign_key_list_ds
943   @_foreign_key_list_ds ||= __foreign_key_list_ds(false)
944 end

_indexes_ds() click to toggle source

Dataset used to retrieve index information

     # File lib/sequel/adapters/shared/postgres.rb
1030 def _indexes_ds
1031   @_indexes_ds ||= begin
1032     if server_version >= 90500
1033       order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
1034     # :nocov:
1035     else
1036       range = 0...32
1037       order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
1038     # :nocov:
1039     end
1040 
1041     attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey])
1042 
1043     ds = metadata_dataset.
1044       from{pg_class.as(:tab)}.
1045       join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
1046       join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
1047       join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
1048       left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
1049       where{{
1050         indc[:relkind]=>%w'i I',
1051         ind[:indisprimary]=>false,
1052         :indexprs=>nil,
1053         :indisvalid=>true}}.
1054       order(*order).
1055       select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}
1056 
1057     # :nocov:
1058     ds = ds.where(:indisready=>true) if server_version >= 80300
1059     ds = ds.where(:indislive=>true) if server_version >= 90300
1060     # :nocov:
1061 
1062     ds
1063   end
1064 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
947 def _reverse_foreign_key_list_ds
948   @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true)
949 end

_schema_ds() click to toggle source

Dataset used to get schema for tables

     # File lib/sequel/adapters/shared/postgres.rb
1127 def _schema_ds
1128   @_schema_ds ||= begin
1129     ds = metadata_dataset.select{[
1130         pg_attribute[:attname].as(:name),
1131         SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid),
1132         SQL::Cast.new(basetype[:oid], :integer).as(:base_oid),
1133         SQL::Function.new(:col_description, pg_class[:oid], pg_attribute[:attnum]).as(:comment),
1134         SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type),
1135         SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type),
1136         SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default),
1137         SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null),
1138         SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key),
1139         Sequel[:pg_type][:typtype],
1140         (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array),
1141       ]}.
1142       from(:pg_class).
1143       join(:pg_attribute, :attrelid=>:oid).
1144       join(:pg_type, :oid=>:atttypid).
1145       left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype).
1146       left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]).
1147       left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]).
1148       left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true).
1149       where{{pg_attribute[:attisdropped]=>false}}.
1150       where{pg_attribute[:attnum] > 0}.
1151       order{pg_attribute[:attnum]}
1152 
1153     # :nocov:
1154     if server_version > 100000
1155     # :nocov:
1156       ds = ds.select_append{pg_attribute[:attidentity]}
1157 
1158       # :nocov:
1159       if server_version > 120000
1160       # :nocov:
1161         ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)}
1162       end
1163     end
1164 
1165     ds
1166   end
1167 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
1067 def _select_custom_sequence_ds
1068   @_select_custom_sequence_ds ||= metadata_dataset.
1069     from{pg_class.as(:t)}.
1070     join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name).
1071     join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr).
1072     join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def).
1073     join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons).
1074     where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}.
1075     select{
1076       expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2)
1077       [
1078         name[:nspname].as(:schema),
1079         Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence)
1080       ]
1081     }
1082 end

_select_pk_ds() click to toggle source

Dataset used to determine primary keys for tables

     # File lib/sequel/adapters/shared/postgres.rb
1113 def _select_pk_ds
1114   @_select_pk_ds ||= metadata_dataset.
1115     from(:pg_class, :pg_attribute, :pg_index, :pg_namespace).
1116     where{[
1117       [pg_class[:oid], pg_attribute[:attrelid]],
1118       [pg_class[:relnamespace], pg_namespace[:oid]],
1119       [pg_class[:oid], pg_index[:indrelid]],
1120       [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]],
1121       [pg_index[:indisprimary], 't']
1122     ]}.
1123     select{pg_attribute[:attname].as(:pk)}
1124 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
1085 def _select_serial_sequence_ds
1086   @_serial_sequence_ds ||= metadata_dataset.
1087     from{[
1088       pg_class.as(:seq),
1089       pg_attribute.as(:attr),
1090       pg_depend.as(:dep),
1091       pg_namespace.as(:name),
1092       pg_constraint.as(:cons),
1093       pg_class.as(:t)
1094     ]}.
1095     where{[
1096       [seq[:oid], dep[:objid]],
1097       [seq[:relnamespace], name[:oid]],
1098       [seq[:relkind], 'S'],
1099       [attr[:attrelid], dep[:refobjid]],
1100       [attr[:attnum], dep[:refobjsubid]],
1101       [attr[:attrelid], cons[:conrelid]],
1102       [attr[:attnum], cons[:conkey].sql_subscript(1)],
1103       [attr[:attrelid], t[:oid]],
1104       [cons[:contype], 'p']
1105     ]}.
1106     select{[
1107       name[:nspname].as(:schema),
1108       seq[:relname].as(:sequence)
1109     ]}
1110 end

_set_constraints(type, opts) click to toggle source

Internals of defer_constraints/immediate_constraints

     # File lib/sequel/adapters/shared/postgres.rb
1170 def _set_constraints(type, opts)
1171   execute_ddl(_set_constraints_sql(type, opts), opts)
1172 end

_set_constraints_sql(type, opts) click to toggle source

SQL to use for SET CONSTRAINTS

     # File lib/sequel/adapters/shared/postgres.rb
1175 def _set_constraints_sql(type, opts)
1176   sql = String.new
1177   sql << "SET CONSTRAINTS "
1178   if constraints = opts[:constraints]
1179     dataset.send(:source_list_append, sql, Array(constraints))
1180   else
1181     sql << "ALL"
1182   end
1183   sql << type
1184 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
1188 def _table_exists?(ds)
1189   super
1190 rescue DatabaseError => e    
1191   raise e unless /canceling statement due to (?:statement|lock) timeout/ =~ e.message 
1192 end

alter_table_add_column_sql(table, op) click to toggle source

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

alter_table_alter_constraint_sql(table, op) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1198 def alter_table_alter_constraint_sql(table, op)
1199   sql = String.new
1200   sql << "ALTER CONSTRAINT #{quote_identifier(op[:name])}"
1201   
1202   constraint_deferrable_sql_append(sql, op[:deferrable])
1203 
1204   case op[:enforced]
1205   when nil
1206   when false
1207     sql << " NOT ENFORCED"
1208   else
1209     sql << " ENFORCED"
1210   end
1211 
1212   case op[:inherit]
1213   when nil
1214   when false
1215     sql << " NO INHERIT"
1216   else
1217     sql << " INHERIT"
1218   end
1219 
1220   sql
1221 end

alter_table_drop_column_sql(table, op) click to toggle source

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

alter_table_generator_class() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1223 def alter_table_generator_class
1224   Postgres::AlterTableGenerator
1225 end

alter_table_rename_constraint_sql(table, op) click to toggle source

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

alter_table_set_column_type_sql(table, op) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1231 def alter_table_set_column_type_sql(table, op)
1232   s = super
1233   if using = op[:using]
1234     using = Sequel::LiteralString.new(using) if using.is_a?(String)
1235     s += ' USING '
1236     s << literal(using)
1237   end
1238   s
1239 end

alter_table_validate_constraint_sql(table, op) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1245 def alter_table_validate_constraint_sql(table, op)
1246   "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
1247 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
1252 def begin_new_transaction(conn, opts)
1253   super
1254   if opts.has_key?(:synchronous)
1255     case sync = opts[:synchronous]
1256     when true
1257       sync = :on
1258     when false
1259       sync = :off
1260     when nil
1261       return
1262     end
1263 
1264     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
1265   end
1266 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
1269 def begin_savepoint(conn, opts)
1270   super
1271 
1272   unless (read_only = opts[:read_only]).nil?
1273     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
1274   end
1275 end

column_definition_add_references_sql(sql, column) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1423 def column_definition_add_references_sql(sql, column)
1424   super
1425   if column[:not_enforced]
1426     sql << " NOT ENFORCED"
1427   end
1428 end

column_definition_append_include_sql(sql, constraint) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1277 def column_definition_append_include_sql(sql, constraint)
1278   if include_cols = constraint[:include]
1279     sql << " INCLUDE " << literal(Array(include_cols))
1280   end
1281 end

column_definition_append_primary_key_sql(sql, constraint) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1283 def column_definition_append_primary_key_sql(sql, constraint)
1284   super
1285   column_definition_append_include_sql(sql, constraint)
1286 end

column_definition_append_unique_sql(sql, constraint) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1288 def column_definition_append_unique_sql(sql, constraint)
1289   super
1290   column_definition_append_include_sql(sql, constraint)
1291 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
1295 def column_definition_collate_sql(sql, column)
1296   if collate = column[:collate]
1297     collate = literal(collate) unless collate.is_a?(String)
1298     sql << " COLLATE #{collate}"
1299   end
1300 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
1304 def column_definition_default_sql(sql, column)
1305   super
1306   if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
1307     if (identity = column[:identity])
1308       sql << " GENERATED "
1309       sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
1310       sql << " AS IDENTITY"
1311     elsif (generated = column[:generated_always_as])
1312       sql << " GENERATED ALWAYS AS (#{literal(generated)}) #{column[:virtual] ? 'VIRTUAL' : 'STORED'}"
1313     end
1314   end
1315 end

column_definition_null_sql(sql, column) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1430 def column_definition_null_sql(sql, column)
1431   constraint = column[:not_null]
1432   constraint = nil unless constraint.is_a?(Hash)
1433   if constraint && (name = constraint[:name])
1434     sql << " CONSTRAINT #{quote_identifier(name)}"
1435   end
1436   super
1437   if constraint && constraint[:no_inherit]
1438     sql << " NO INHERIT"
1439   end
1440 end

column_references_add_period(cols) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1457 def column_references_add_period(cols)
1458   cols= cols.dup
1459   cols[-1] = Sequel.lit("PERIOD #{quote_identifier(cols[-1])}")
1460   cols
1461 end

column_references_append_key_sql(sql, column) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1451 def column_references_append_key_sql(sql, column)
1452   cols = Array(column[:key])
1453   cols = column_references_add_period(cols) if column[:period]
1454   sql << "(#{cols.map{|x| quote_identifier(x)}.join(', ')})"
1455 end

column_references_table_constraint_sql(constraint) click to toggle source

Handle :period option

     # File lib/sequel/adapters/shared/postgres.rb
1443 def column_references_table_constraint_sql(constraint)
1444   sql = String.new
1445   sql << "FOREIGN KEY "
1446   cols = constraint[:columns]
1447   cols = column_references_add_period(cols) if constraint[:period]
1448   sql << literal(cols) << column_references_sql(constraint)
1449 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
1318 def column_schema_normalize_default(default, type)
1319   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
1320     default = m[1] || m[2]
1321   end
1322   super(default, type)
1323 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
1337 def combinable_alter_table_op?(op)
1338   (super || op[:op] == :validate_constraint || op[:op] == :alter_constraint) && op[:op] != :rename_column
1339 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
1327 def commit_transaction(conn, opts=OPTS)
1328   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
1329     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
1330   else
1331     super
1332   end
1333 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
1343 def connection_configuration_sqls(opts=@opts)
1344   sqls = []
1345 
1346   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
1347 
1348   cmm = opts.fetch(:client_min_messages, :warning)
1349   if cmm && !cmm.to_s.empty?
1350     cmm = cmm.to_s.upcase.strip
1351     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
1352       raise Error, "Unsupported client_min_messages setting: #{cmm}"
1353     end
1354     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
1355   end
1356 
1357   if search_path = opts[:search_path]
1358     case search_path
1359     when String
1360       search_path = search_path.split(",").map(&:strip)
1361     when Array
1362       # nil
1363     else
1364       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
1365     end
1366     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
1367   end
1368 
1369   sqls
1370 end

constraint_definition_sql(constraint) click to toggle source

Handle PostgreSQL-specific constraint features.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1373 def constraint_definition_sql(constraint)
1374   case type = constraint[:type]
1375   when :exclude
1376     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
1377     sql = String.new
1378     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
1379     sql << "EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})"
1380     column_definition_append_include_sql(sql, constraint)
1381     sql << " WHERE #{filter_expr(constraint[:where])}" if constraint[:where]
1382     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1383     sql
1384   when :primary_key, :unique
1385     sql = String.new
1386     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
1387 
1388     if type == :primary_key
1389       sql << primary_key_constraint_sql_fragment(constraint)
1390     else
1391       sql << unique_constraint_sql_fragment(constraint)
1392     end
1393 
1394     if using_index = constraint[:using_index]
1395       sql << " USING INDEX " << quote_identifier(using_index)
1396     else
1397       cols = literal(constraint[:columns])
1398       cols.insert(-2, " WITHOUT OVERLAPS") if constraint[:without_overlaps]
1399       sql << " " << cols
1400 
1401       if include_cols = constraint[:include]
1402         sql << " INCLUDE " << literal(Array(include_cols))
1403       end
1404     end
1405 
1406     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1407     sql
1408   else # when :foreign_key, :check
1409     sql = super
1410     if constraint[:no_inherit]
1411       sql << " NO INHERIT"
1412     end
1413     if constraint[:not_enforced]
1414       sql << " NOT ENFORCED"
1415     end
1416     if constraint[:not_valid]
1417       sql << " NOT VALID"
1418     end
1419     sql
1420   end
1421 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
1492 def copy_into_sql(table, opts)
1493   sql = String.new
1494   sql << "COPY #{literal(table)}"
1495   if cols = opts[:columns]
1496     sql << literal(Array(cols))
1497   end
1498   sql << " FROM STDIN"
1499   if opts[:options] || opts[:format]
1500     sql << " ("
1501     sql << "FORMAT #{opts[:format]}" if opts[:format]
1502     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1503     sql << ')'
1504   end
1505   sql
1506 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
1509 def copy_table_sql(table, opts)
1510   if table.is_a?(String)
1511     table
1512   else
1513     if opts[:options] || opts[:format]
1514       options = String.new
1515       options << " ("
1516       options << "FORMAT #{opts[:format]}" if opts[:format]
1517       options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1518       options << ')'
1519     end
1520     table = if table.is_a?(::Sequel::Dataset)
1521       "(#{table.sql})"
1522     else
1523       literal(table)
1524     end
1525     "COPY #{table} TO STDOUT#{options}"
1526   end
1527 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
1530       def create_function_sql(name, definition, opts=OPTS)
1531         args = opts[:args]
1532         in_out = %w'OUT INOUT'
1533         if (!opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 && in_out.include?(a[2].to_s)})
1534           returns = opts[:returns] || 'void'
1535         end
1536         language = opts[:language] || 'SQL'
1537         <<-END
1538         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
1539         #{"RETURNS #{returns}" if returns}
1540         LANGUAGE #{language}
1541         #{opts[:behavior].to_s.upcase if opts[:behavior]}
1542         #{'STRICT' if opts[:strict]}
1543         #{'SECURITY DEFINER' if opts[:security_definer]}
1544         #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]}
1545         #{"COST #{opts[:cost]}" if opts[:cost]}
1546         #{"ROWS #{opts[:rows]}" if opts[:rows]}
1547         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
1548         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
1549         END
1550       end

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

SQL for creating a procedural language.

     # File lib/sequel/adapters/shared/postgres.rb
1553 def create_language_sql(name, opts=OPTS)
1554   "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]}"
1555 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
1559 def create_partition_of_table_from_generator(name, generator, options)
1560   execute_ddl(create_partition_of_table_sql(name, generator, options))
1561 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
1564 def create_partition_of_table_sql(name, generator, options)
1565   sql = create_table_prefix_sql(name, options).dup
1566 
1567   sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}"
1568 
1569   case generator.partition_type
1570   when :range
1571     from, to = generator.range
1572     sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}"
1573   when :list
1574     sql << " FOR VALUES IN #{literal(generator.list)}"
1575   when :hash
1576     mod, remainder = generator.hash_values
1577     sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})"
1578   else # when :default
1579     sql << " DEFAULT"
1580   end
1581 
1582   sql << create_table_suffix_sql(name, options)
1583 
1584   sql
1585 end

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

SQL for creating a schema.

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

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

     # File lib/sequel/adapters/shared/postgres.rb
1646 def create_table_as_sql(name, sql, options)
1647   result = create_table_prefix_sql name, options
1648   if on_commit = options[:on_commit]
1649     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
1650   end
1651   result += " AS #{sql}"
1652 end

create_table_generator_class() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1654 def create_table_generator_class
1655   Postgres::CreateTableGenerator
1656 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
1593 def create_table_prefix_sql(name, options)
1594   prefix_sql = if options[:temp]
1595     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
1596     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
1597     temporary_table_sql
1598   elsif options[:foreign]
1599     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
1600     'FOREIGN '
1601   elsif options.fetch(:unlogged){typecast_value_boolean(@opts[:unlogged_tables_default])}
1602     'UNLOGGED '
1603   end
1604 
1605   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{create_table_table_name_sql(name, options)}"
1606 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
1609 def create_table_sql(name, generator, options)
1610   "#{super}#{create_table_suffix_sql(name, options)}"
1611 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
1615 def create_table_suffix_sql(name, options)
1616   sql = String.new
1617 
1618   if inherits = options[:inherits]
1619     sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
1620   end
1621 
1622   if partition_by = options[:partition_by]
1623     sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
1624   end
1625 
1626   if on_commit = options[:on_commit]
1627     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
1628     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
1629     sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
1630   end
1631 
1632   if tablespace = options[:tablespace]
1633     sql << " TABLESPACE #{quote_identifier(tablespace)}"
1634   end
1635 
1636   if server = options[:foreign]
1637     sql << " SERVER #{quote_identifier(server)}"
1638     if foreign_opts = options[:options]
1639       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
1640     end
1641   end
1642 
1643   sql
1644 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
1659 def create_trigger_sql(table, name, function, opts=OPTS)
1660   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
1661   whence = opts[:after] ? 'AFTER' : 'BEFORE'
1662   if filter = opts[:when]
1663     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
1664     filter = " WHEN #{filter_expr(filter)}"
1665   end
1666   "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(', ')})"
1667 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
1670 def create_view_prefix_sql(name, options)
1671   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])
1672 
1673   if options[:security_invoker]
1674     sql += " WITH (security_invoker)"
1675   end
1676 
1677   if tablespace = options[:tablespace]
1678     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1679   end
1680 
1681   sql
1682 end

database_error_regexps() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1487 def database_error_regexps
1488   DATABASE_ERROR_REGEXPS
1489 end

database_specific_error_class_from_sqlstate(sqlstate) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1463 def database_specific_error_class_from_sqlstate(sqlstate)
1464   if sqlstate == '23P01'
1465     ExclusionConstraintViolation
1466   elsif sqlstate == '40P01'
1467     SerializationFailure
1468   elsif sqlstate == '55P03'
1469     DatabaseLockTimeout
1470   else
1471     super
1472   end
1473 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
1685 def drop_function_sql(name, opts=OPTS)
1686   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
1687 end

drop_index_sql(table, op) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1690 def drop_index_sql(table, op)
1691   sch, _ = schema_and_table(table)
1692   "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]}"
1693 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
1696 def drop_language_sql(name, opts=OPTS)
1697   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1698 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
1701 def drop_schema_sql(name, opts=OPTS)
1702   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1703 end

drop_table_sql(name, options) click to toggle source

Support :foreign tables

     # File lib/sequel/adapters/shared/postgres.rb
1711 def drop_table_sql(name, options)
1712   "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
1713 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
1706 def drop_trigger_sql(table, name, opts=OPTS)
1707   "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
1708 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
1716 def drop_view_sql(name, opts=OPTS)
1717   "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
1718 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
1722 def filter_schema(ds, opts)
1723   expr = if schema = opts[:schema]
1724     if schema.is_a?(SQL::Identifier)
1725       schema.value.to_s
1726     else
1727       schema.to_s
1728     end
1729   else
1730     Sequel.function(:any, Sequel.function(:current_schemas, false))
1731   end
1732   ds.where{{pg_namespace[:nspname]=>expr}}
1733 end

index_definition_sql(table_name, index) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1735 def index_definition_sql(table_name, index)
1736   cols = index[:columns]
1737   index_name = index[:name] || default_index_name(table_name, cols)
1738 
1739   expr = if o = index[:opclass]
1740     "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
1741   else
1742     literal(Array(cols))
1743   end
1744 
1745   if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
1746   unique = "UNIQUE " if index[:unique]
1747   index_type = index[:type]
1748   filter = index[:where] || index[:filter]
1749   filter = " WHERE #{filter_expr(filter)}" if filter
1750   nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil?
1751 
1752   case index_type
1753   when :full_text
1754     expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
1755     index_type = index[:index_type] || :gin
1756   when :spatial
1757     index_type = :gist
1758   end
1759 
1760   "CREATE #{unique}INDEX#{' CONCURRENTLY' if index[:concurrently]}#{if_not_exists} #{quote_identifier(index_name)} ON #{quote_schema_table(table_name)} #{"USING #{index_type} " if index_type}#{expr}#{" INCLUDE #{literal(Array(index[:include]))}" if index[:include]}#{nulls_distinct}#{" TABLESPACE #{quote_identifier(index[:tablespace])}" if index[:tablespace]}#{filter}"
1761 end

initialize_postgres_adapter() click to toggle source

Setup datastructures shared by all postgres adapters.

     # File lib/sequel/adapters/shared/postgres.rb
1764 def initialize_postgres_adapter
1765   @primary_keys = {}
1766   @primary_key_sequences = {}
1767   @supported_types = {}
1768   procs = @conversion_procs = CONVERSION_PROCS.dup
1769   procs[1184] = procs[1114] = method(:to_application_timestamp)
1770 end

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

Backbone of the tables and views support.

     # File lib/sequel/adapters/shared/postgres.rb
1773 def pg_class_relname(type, opts)
1774   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
1775   ds = filter_schema(ds, opts)
1776   m = output_identifier_meth
1777   if defined?(yield)
1778     yield(ds)
1779   elsif opts[:qualify]
1780     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
1781   else
1782     ds.map{|r| m.call(r[:relname])}
1783   end
1784 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
1788 def regclass_oid(expr, opts=OPTS)
1789   if expr.is_a?(String) && !expr.is_a?(LiteralString)
1790     expr = Sequel.identifier(expr)
1791   end
1792 
1793   sch, table = schema_and_table(expr)
1794   sch ||= opts[:schema]
1795   if sch
1796     expr = Sequel.qualify(sch, table)
1797   end
1798   
1799   expr = if ds = opts[:dataset]
1800     ds.literal(expr)
1801   else
1802     literal(expr)
1803   end
1804 
1805   Sequel.cast(expr.to_s,:regclass).cast(:oid)
1806 end

remove_all_cached_schemas() click to toggle source

Clear all cached schema information

     # File lib/sequel/adapters/shared/postgres.rb
1819 def remove_all_cached_schemas
1820   @primary_keys.clear
1821   @primary_key_sequences.clear
1822   @schemas.clear
1823 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
1809 def remove_cached_schema(table)
1810   tab = quote_schema_table(table)
1811   Sequel.synchronize do
1812     @primary_keys.delete(tab)
1813     @primary_key_sequences.delete(tab)
1814   end
1815   super
1816 end

rename_schema_sql(name, new_name) click to toggle source

SQL for renaming a schema.

     # File lib/sequel/adapters/shared/postgres.rb
1826 def rename_schema_sql(name, new_name)
1827   "ALTER SCHEMA #{quote_identifier(name)} RENAME TO #{quote_identifier(new_name)}"
1828 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
1832 def rename_table_sql(name, new_name)
1833   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
1834 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
1849 def schema_array_type(db_type)
1850   :array
1851 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
1837 def schema_column_type(db_type)
1838   case db_type
1839   when /\Ainterval\z/i
1840     :interval
1841   when /\Acitext\z/i
1842     :string
1843   else
1844     super
1845   end
1846 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
1854 def schema_composite_type(db_type)
1855   :composite
1856 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
1859 def schema_enum_type(db_type)
1860   :enum
1861 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
1869 def schema_multirange_type(db_type)
1870   :multirange
1871 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
1886 def schema_parse_table(table_name, opts)
1887   m = output_identifier_meth(opts[:dataset])
1888 
1889   _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row|
1890     row[:default] = nil if blank_object?(row[:default])
1891     if row[:base_oid]
1892       row[:domain_oid] = row[:oid]
1893       row[:oid] = row.delete(:base_oid)
1894       row[:db_domain_type] = row[:db_type]
1895       row[:db_type] = row.delete(:db_base_type)
1896     else
1897       row.delete(:base_oid)
1898       row.delete(:db_base_type)
1899     end
1900 
1901     db_type = row[:db_type]
1902     row[:type] = if row.delete(:is_array)
1903       schema_array_type(db_type)
1904     else
1905       send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type)
1906     end
1907     identity = row.delete(:attidentity)
1908     if row[:primary_key]
1909       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
1910     end
1911 
1912     # :nocov:
1913     if server_version >= 90600
1914     # :nocov:
1915       case row[:oid]
1916       when 1082
1917         row[:min_value] = MIN_DATE
1918         row[:max_value] = MAX_DATE
1919       when 1184, 1114
1920         if Sequel.datetime_class == Time
1921           row[:min_value] = MIN_TIMESTAMP
1922           row[:max_value] = MAX_TIMESTAMP
1923         end
1924       end
1925     end
1926 
1927     [m.call(row.delete(:name)), row]
1928   end
1929 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
1864 def schema_range_type(db_type)
1865   :range
1866 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
1932 def set_transaction_isolation(conn, opts)
1933   level = opts.fetch(:isolation, transaction_isolation_level)
1934   read_only = opts[:read_only]
1935   deferrable = opts[:deferrable]
1936   if level || !read_only.nil? || !deferrable.nil?
1937     sql = String.new
1938     sql << "SET TRANSACTION"
1939     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
1940     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
1941     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
1942     log_connection_execute(conn, sql)
1943   end
1944 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
1947 def sql_function_args(args)
1948   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
1949 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
1952 def supports_combining_alter_table_ops?
1953   true
1954 end

supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1957 def supports_create_or_replace_view?
1958   true
1959 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
1962 def type_literal_generic_bignum_symbol(column)
1963   column[:serial] ? :bigserial : super
1964 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
1967 def type_literal_generic_file(column)
1968   :bytea
1969 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
1972 def type_literal_generic_integer(column)
1973   column[:serial] ? :serial : super
1974 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
1980 def type_literal_generic_string(column)
1981   if column[:text]
1982     :text
1983   elsif column[:fixed]
1984     "char(#{column[:size]||default_string_column_size})"
1985   elsif column[:text] == false || column[:size]
1986     "varchar(#{column[:size]||default_string_column_size})"
1987   else
1988     :text
1989   end
1990 end

unique_constraint_sql_fragment(constraint) click to toggle source

Support :nulls_not_distinct option.

     # File lib/sequel/adapters/shared/postgres.rb
1993 def unique_constraint_sql_fragment(constraint)
1994   if constraint[:nulls_not_distinct]
1995     'UNIQUE NULLS NOT DISTINCT'
1996   else
1997     'UNIQUE'
1998   end
1999 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
2002 def view_with_check_option_support
2003   # :nocov:
2004   :local if server_version >= 90400
2005   # :nocov:
2006 end