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
322 def add_conversion_proc(oid, callable=nil, &block)
323   conversion_procs[oid] = callable || block
324 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
329 def add_named_conversion_proc(name, &block)
330   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
331     raise Error, "No matching type in pg_type for #{name.inspect}"
332   end
333   add_conversion_proc(oid, block)
334 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
345 def check_constraints(table)
346   m = output_identifier_meth
347 
348   hash = {}
349   _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row|
350     constraint = m.call(row[:constraint])
351     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[], :validated=>row[:validated], :enforced=>row[:enforced]}
352     entry[:columns] << m.call(row[:column]) if row[:column]
353   end
354   
355   hash
356 end

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

    # File lib/sequel/adapters/shared/postgres.rb
336 def commit_prepared_transaction(transaction_id, opts=OPTS)
337   run("COMMIT PREPARED #{literal(transaction_id)}", opts)
338 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
376 def convert_serial_to_identity(table, opts=OPTS)
377   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
378 
379   server = opts[:server]
380   server_hash = server ? {:server=>server} : OPTS
381   ds = dataset
382   ds = ds.server(server) if server
383 
384   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
385 
386   table_oid = regclass_oid(table)
387   im = input_identifier_meth
388   unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
389     raise Error, "could not determine column to convert from serial to identity automatically"
390   end
391   column = im.call(column)
392 
393   column_num = ds.from(:pg_attribute).
394     where(:attrelid=>table_oid, :attname=>column).
395     get(:attnum)
396 
397   pg_class = Sequel.cast('pg_class', :regclass)
398   res = ds.from(:pg_depend).
399     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
400     select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])
401 
402   case res.length
403   when 0
404     raise Error, "unable to find related sequence when converting serial to identity"
405   when 1
406     seq_oid, already_identity = res.first
407   else
408     raise Error, "more than one linked sequence found when converting serial to identity"
409   end
410 
411   return if already_identity
412 
413   transaction(server_hash) do
414     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash)
415 
416     ds.from(:pg_depend).
417       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
418       update(:deptype=>'i')
419 
420     ds.from(:pg_attribute).
421       where(:attrelid=>table_oid, :attname=>column).
422       update(:attidentity=>'d')
423   end
424 
425   remove_cached_schema(table)
426   nil
427 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
450 def create_function(name, definition, opts=OPTS)
451   self << create_function_sql(name, definition, opts)
452 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
461 def create_language(name, opts=OPTS)
462   self << create_language_sql(name, opts)
463 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
470 def create_schema(name, opts=OPTS)
471   self << create_schema_sql(name, opts)
472 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
475 def create_table(name, options=OPTS, &block)
476   if options[:partition_of]
477     create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
478     return
479   end
480 
481   super
482 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
485 def create_table?(name, options=OPTS, &block)
486   if options[:partition_of]
487     create_table(name, options.merge!(:if_not_exists=>true), &block)
488     return
489   end
490 
491   super
492 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
506 def create_trigger(table, name, function, opts=OPTS)
507   self << create_trigger_sql(table, name, function, opts)
508 end

database_type() click to toggle source

    # File lib/sequel/adapters/shared/postgres.rb
510 def database_type
511   :postgres
512 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
529 def defer_constraints(opts=OPTS)
530   _set_constraints(' DEFERRED', opts)
531 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
538 def do(code, opts=OPTS)
539   language = opts[:language]
540   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}"
541 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
549 def drop_function(name, opts=OPTS)
550   self << drop_function_sql(name, opts)
551 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
558 def drop_language(name, opts=OPTS)
559   self << drop_language_sql(name, opts)
560 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
567 def drop_schema(name, opts=OPTS)
568   self << drop_schema_sql(name, opts)
569 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
577 def drop_trigger(table, name, opts=OPTS)
578   self << drop_trigger_sql(table, name, opts)
579 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
591 def foreign_key_list(table, opts=OPTS)
592   m = output_identifier_meth
593   schema, _ = opts.fetch(:schema, schema_and_table(table))
594 
595   h = {}
596   fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
597   reverse = opts[:reverse]
598 
599   (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row|
600     if reverse
601       key = [row[:schema], row[:table], row[:name]]
602     else
603       key = row[:name]
604     end
605 
606     if r = h[key]
607       r[:columns] << m.call(row[:column])
608       r[:key] << m.call(row[:refcolumn])
609     else
610       entry = h[key] = {
611         :name=>m.call(row[:name]),
612         :columns=>[m.call(row[:column])],
613         :key=>[m.call(row[:refcolumn])],
614         :on_update=>fklod_map[row[:on_update]],
615         :on_delete=>fklod_map[row[:on_delete]],
616         :deferrable=>row[:deferrable],
617         :validated=>row[:validated],
618         :enforced=>row[:enforced],
619         :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
620       }
621 
622       unless schema
623         # If not combining schema information into the :table entry
624         # include it as a separate entry.
625         entry[:schema] = m.call(row[:schema])
626       end
627     end
628   end
629 
630   h.values
631 end

freeze() click to toggle source

Calls superclass method

    # File lib/sequel/adapters/shared/postgres.rb
633 def freeze
634   server_version
635   supports_prepared_transactions?
636   _schema_ds
637   _select_serial_sequence_ds
638   _select_custom_sequence_ds
639   _select_pk_ds
640   _indexes_ds
641   _check_constraints_ds
642   _foreign_key_list_ds
643   _reverse_foreign_key_list_ds
644   @conversion_procs.freeze
645   super
646 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
662 def immediate_constraints(opts=OPTS)
663   _set_constraints(' IMMEDIATE', opts)
664 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
667 def indexes(table, opts=OPTS)
668   m = output_identifier_meth
669   cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)}
670   cond[:indpred] = nil unless opts[:include_partial]
671 
672   indexes = {}
673   _indexes_ds.where_each(cond) do |r|
674     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
675     i[:columns] << m.call(r[:column])
676   end
677   indexes
678 end

locks() click to toggle source

Dataset containing all current database locks

    # File lib/sequel/adapters/shared/postgres.rb
681 def locks
682   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
683 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
691 def notify(channel, opts=OPTS)
692   sql = String.new
693   sql << "NOTIFY "
694   dataset.send(:identifier_append, sql, channel)
695   if payload = opts[:payload]
696     sql << ", "
697     dataset.literal_append(sql, payload.to_s)
698   end
699   execute_ddl(sql, opts)
700 end

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

Return primary key for the given table.

    # File lib/sequel/adapters/shared/postgres.rb
703 def primary_key(table, opts=OPTS)
704   quoted_table = quote_schema_table(table)
705   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
706   value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts))
707   Sequel.synchronize{@primary_keys[quoted_table] = value}
708 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
711 def primary_key_sequence(table, opts=OPTS)
712   quoted_table = quote_schema_table(table)
713   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
714   cond = {Sequel[:t][:oid] => regclass_oid(table, opts)}
715   value = if pks = _select_serial_sequence_ds.first(cond)
716     literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
717   elsif pks = _select_custom_sequence_ds.first(cond)
718     literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
719   end
720 
721   Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value
722 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
730 def refresh_view(name, opts=OPTS)
731   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
732 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
736 def reset_primary_key_sequence(table)
737   return unless seq = primary_key_sequence(table)
738   pk = SQL::Identifier.new(primary_key(table))
739   db = self
740   s, t = schema_and_table(table)
741   table = Sequel.qualify(s, t) if s
742 
743   if server_version >= 100000
744     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq)))
745     increment_by = :seqincrement
746     min_value = :seqmin
747   # :nocov:
748   else
749     seq_ds = metadata_dataset.from(LiteralString.new(seq))
750     increment_by = :increment_by
751     min_value = :min_value
752   # :nocov:
753   end
754 
755   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
756 end

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

    # File lib/sequel/adapters/shared/postgres.rb
758 def rollback_prepared_transaction(transaction_id, opts=OPTS)
759   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
760 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
764 def serial_primary_key_options
765   # :nocov:
766   auto_increment_key = server_version >= 100002 ? :identity : :serial
767   # :nocov:
768   {:primary_key => true, auto_increment_key => true, :type=>Integer}
769 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
772 def server_version(server=nil)
773   return @server_version if @server_version
774   ds = dataset
775   ds = ds.server(server) if server
776   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
777 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
780 def supports_create_table_if_not_exists?
781   server_version >= 90100
782 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
785 def supports_deferrable_constraints?
786   server_version >= 90000
787 end

supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

    # File lib/sequel/adapters/shared/postgres.rb
790 def supports_deferrable_foreign_key_constraints?
791   true
792 end

supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

    # File lib/sequel/adapters/shared/postgres.rb
795 def supports_drop_table_if_exists?
796   true
797 end

supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

    # File lib/sequel/adapters/shared/postgres.rb
800 def supports_partial_indexes?
801   true
802 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
811 def supports_prepared_transactions?
812   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
813   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
814 end

supports_savepoints?() click to toggle source

PostgreSQL supports savepoints

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

supports_transaction_isolation_levels?() click to toggle source

PostgreSQL supports transaction isolation levels

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

supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

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

supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
805 def supports_trigger_conditions?
806   server_version >= 90000
807 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
840 def tables(opts=OPTS, &block)
841   pg_class_relname(['r', 'p'], opts, &block)
842 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
846 def type_supported?(type)
847   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
848   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
849   Sequel.synchronize{return @supported_types[type] = supported}
850 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
859 def values(v)
860   raise Error, "Cannot provide an empty array for values" if v.empty?
861   @default_dataset.clone(:values=>v)
862 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
872 def views(opts=OPTS)
873   relkind = opts[:materialized] ? 'm' : 'v'
874   pg_class_relname(relkind, opts)
875 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
888 def with_advisory_lock(lock_id, opts=OPTS)
889   ds = dataset
890   if server = opts[:server]
891     ds = ds.server(server)
892   end
893 
894   synchronize(server) do |c|
895     begin
896       if opts[:wait]
897         ds.get{pg_advisory_lock(lock_id)}
898         locked = true
899       else
900         unless locked = ds.get{pg_try_advisory_lock(lock_id)}
901           raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}"
902         end
903       end
904 
905       yield
906     ensure
907       ds.get{pg_advisory_unlock(lock_id)} if locked
908     end
909   end
910 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
938 def __foreign_key_list_ds(reverse)
939   if reverse
940     ctable = Sequel[:att2]
941     cclass = Sequel[:cl2]
942     rtable = Sequel[:att]
943     rclass = Sequel[:cl]
944   else
945     ctable = Sequel[:att]
946     cclass = Sequel[:cl]
947     rtable = Sequel[:att2]
948     rclass = Sequel[:cl2]
949   end
950 
951   if server_version >= 90500
952     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
953     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
954   # :nocov:
955   else
956     range = 0...32
957     cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
958     rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
959   # :nocov:
960   end
961 
962   ds = metadata_dataset.
963     from{pg_constraint.as(:co)}.
964     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
965     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
966     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
967     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
968     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
969     order{[co[:conname], cpos]}.
970     where{{
971       cl[:relkind]=>%w'r p',
972       co[:contype]=>'f',
973       cpos=>rpos
974     }}.
975     select{[
976       co[:conname].as(:name),
977       ctable[:attname].as(:column),
978       co[:confupdtype].as(:on_update),
979       co[:confdeltype].as(:on_delete),
980       cl2[:relname].as(:table),
981       rtable[:attname].as(:refcolumn),
982       SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
983       nsp[:nspname].as(:schema)
984     ]}
985 
986   if reverse
987     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
988   end
989 
990   _add_validated_enforced_constraint_columns(ds)
991 end

_add_validated_enforced_constraint_columns(ds) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
 993 def _add_validated_enforced_constraint_columns(ds)
 994   validated_cond = if server_version >= 90100
 995     Sequel[:convalidated]
 996   # :nocov:
 997   else
 998     Sequel.cast(true, TrueClass)
 999   # :nocov:
1000   end
1001   ds = ds.select_append(validated_cond.as(:validated))
1002 
1003   enforced_cond = if server_version >= 180000
1004     Sequel[:conenforced]
1005   # :nocov:
1006   else
1007     Sequel.cast(true, TrueClass)
1008   # :nocov:
1009   end
1010   ds = ds.select_append(enforced_cond.as(:enforced))
1011 
1012   ds
1013 end

_check_constraints_ds() click to toggle source

Dataset used to retrieve CHECK constraint information

    # File lib/sequel/adapters/shared/postgres.rb
915 def _check_constraints_ds
916   @_check_constraints_ds ||= begin
917     ds = metadata_dataset.
918       from{pg_constraint.as(:co)}.
919       left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
920       where(:contype=>'c').
921       select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
922 
923     _add_validated_enforced_constraint_columns(ds)
924   end
925 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
928 def _foreign_key_list_ds
929   @_foreign_key_list_ds ||= __foreign_key_list_ds(false)
930 end

_indexes_ds() click to toggle source

Dataset used to retrieve index information

     # File lib/sequel/adapters/shared/postgres.rb
1016 def _indexes_ds
1017   @_indexes_ds ||= begin
1018     if server_version >= 90500
1019       order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
1020     # :nocov:
1021     else
1022       range = 0...32
1023       order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
1024     # :nocov:
1025     end
1026 
1027     attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey])
1028 
1029     ds = metadata_dataset.
1030       from{pg_class.as(:tab)}.
1031       join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
1032       join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
1033       join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
1034       left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
1035       where{{
1036         indc[:relkind]=>%w'i I',
1037         ind[:indisprimary]=>false,
1038         :indexprs=>nil,
1039         :indisvalid=>true}}.
1040       order(*order).
1041       select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}
1042 
1043     # :nocov:
1044     ds = ds.where(:indisready=>true) if server_version >= 80300
1045     ds = ds.where(:indislive=>true) if server_version >= 90300
1046     # :nocov:
1047 
1048     ds
1049   end
1050 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
933 def _reverse_foreign_key_list_ds
934   @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true)
935 end

_schema_ds() click to toggle source

Dataset used to get schema for tables

     # File lib/sequel/adapters/shared/postgres.rb
1113 def _schema_ds
1114   @_schema_ds ||= begin
1115     ds = metadata_dataset.select{[
1116         pg_attribute[:attname].as(:name),
1117         SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid),
1118         SQL::Cast.new(basetype[:oid], :integer).as(:base_oid),
1119         SQL::Function.new(:col_description, pg_class[:oid], pg_attribute[:attnum]).as(:comment),
1120         SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type),
1121         SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type),
1122         SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default),
1123         SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null),
1124         SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key),
1125         Sequel[:pg_type][:typtype],
1126         (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array),
1127       ]}.
1128       from(:pg_class).
1129       join(:pg_attribute, :attrelid=>:oid).
1130       join(:pg_type, :oid=>:atttypid).
1131       left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype).
1132       left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]).
1133       left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]).
1134       left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true).
1135       where{{pg_attribute[:attisdropped]=>false}}.
1136       where{pg_attribute[:attnum] > 0}.
1137       order{pg_attribute[:attnum]}
1138 
1139     # :nocov:
1140     if server_version > 100000
1141     # :nocov:
1142       ds = ds.select_append{pg_attribute[:attidentity]}
1143 
1144       # :nocov:
1145       if server_version > 120000
1146       # :nocov:
1147         ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)}
1148       end
1149     end
1150 
1151     ds
1152   end
1153 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
1053 def _select_custom_sequence_ds
1054   @_select_custom_sequence_ds ||= metadata_dataset.
1055     from{pg_class.as(:t)}.
1056     join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name).
1057     join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr).
1058     join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def).
1059     join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons).
1060     where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}.
1061     select{
1062       expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2)
1063       [
1064         name[:nspname].as(:schema),
1065         Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence)
1066       ]
1067     }
1068 end

_select_pk_ds() click to toggle source

Dataset used to determine primary keys for tables

     # File lib/sequel/adapters/shared/postgres.rb
1099 def _select_pk_ds
1100   @_select_pk_ds ||= metadata_dataset.
1101     from(:pg_class, :pg_attribute, :pg_index, :pg_namespace).
1102     where{[
1103       [pg_class[:oid], pg_attribute[:attrelid]],
1104       [pg_class[:relnamespace], pg_namespace[:oid]],
1105       [pg_class[:oid], pg_index[:indrelid]],
1106       [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]],
1107       [pg_index[:indisprimary], 't']
1108     ]}.
1109     select{pg_attribute[:attname].as(:pk)}
1110 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
1071 def _select_serial_sequence_ds
1072   @_serial_sequence_ds ||= metadata_dataset.
1073     from{[
1074       pg_class.as(:seq),
1075       pg_attribute.as(:attr),
1076       pg_depend.as(:dep),
1077       pg_namespace.as(:name),
1078       pg_constraint.as(:cons),
1079       pg_class.as(:t)
1080     ]}.
1081     where{[
1082       [seq[:oid], dep[:objid]],
1083       [seq[:relnamespace], name[:oid]],
1084       [seq[:relkind], 'S'],
1085       [attr[:attrelid], dep[:refobjid]],
1086       [attr[:attnum], dep[:refobjsubid]],
1087       [attr[:attrelid], cons[:conrelid]],
1088       [attr[:attnum], cons[:conkey].sql_subscript(1)],
1089       [attr[:attrelid], t[:oid]],
1090       [cons[:contype], 'p']
1091     ]}.
1092     select{[
1093       name[:nspname].as(:schema),
1094       seq[:relname].as(:sequence)
1095     ]}
1096 end

_set_constraints(type, opts) click to toggle source

Internals of defer_constraints/immediate_constraints

     # File lib/sequel/adapters/shared/postgres.rb
1156 def _set_constraints(type, opts)
1157   execute_ddl(_set_constraints_sql(type, opts), opts)
1158 end

_set_constraints_sql(type, opts) click to toggle source

SQL to use for SET CONSTRAINTS

     # File lib/sequel/adapters/shared/postgres.rb
1161 def _set_constraints_sql(type, opts)
1162   sql = String.new
1163   sql << "SET CONSTRAINTS "
1164   if constraints = opts[:constraints]
1165     dataset.send(:source_list_append, sql, Array(constraints))
1166   else
1167     sql << "ALL"
1168   end
1169   sql << type
1170 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
1174 def _table_exists?(ds)
1175   super
1176 rescue DatabaseError => e    
1177   raise e unless /canceling statement due to (?:statement|lock) timeout/ =~ e.message 
1178 end

alter_table_add_column_sql(table, op) click to toggle source

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

alter_table_alter_constraint_sql(table, op) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1184 def alter_table_alter_constraint_sql(table, op)
1185   sql = String.new
1186   sql << "ALTER CONSTRAINT #{quote_identifier(op[:name])}"
1187   
1188   constraint_deferrable_sql_append(sql, op[:deferrable])
1189 
1190   case op[:enforced]
1191   when nil
1192   when false
1193     sql << " NOT ENFORCED"
1194   else
1195     sql << " ENFORCED"
1196   end
1197 
1198   case op[:inherit]
1199   when nil
1200   when false
1201     sql << " NO INHERIT"
1202   else
1203     sql << " INHERIT"
1204   end
1205 
1206   sql
1207 end

alter_table_drop_column_sql(table, op) click to toggle source

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

alter_table_generator_class() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1209 def alter_table_generator_class
1210   Postgres::AlterTableGenerator
1211 end

alter_table_set_column_type_sql(table, op) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1213 def alter_table_set_column_type_sql(table, op)
1214   s = super
1215   if using = op[:using]
1216     using = Sequel::LiteralString.new(using) if using.is_a?(String)
1217     s += ' USING '
1218     s << literal(using)
1219   end
1220   s
1221 end

alter_table_validate_constraint_sql(table, op) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1227 def alter_table_validate_constraint_sql(table, op)
1228   "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
1229 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
1234 def begin_new_transaction(conn, opts)
1235   super
1236   if opts.has_key?(:synchronous)
1237     case sync = opts[:synchronous]
1238     when true
1239       sync = :on
1240     when false
1241       sync = :off
1242     when nil
1243       return
1244     end
1245 
1246     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
1247   end
1248 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
1251 def begin_savepoint(conn, opts)
1252   super
1253 
1254   unless (read_only = opts[:read_only]).nil?
1255     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
1256   end
1257 end

column_definition_add_references_sql(sql, column) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1405 def column_definition_add_references_sql(sql, column)
1406   super
1407   if column[:not_enforced]
1408     sql << " NOT ENFORCED"
1409   end
1410 end

column_definition_append_include_sql(sql, constraint) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1259 def column_definition_append_include_sql(sql, constraint)
1260   if include_cols = constraint[:include]
1261     sql << " INCLUDE " << literal(Array(include_cols))
1262   end
1263 end

column_definition_append_primary_key_sql(sql, constraint) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1265 def column_definition_append_primary_key_sql(sql, constraint)
1266   super
1267   column_definition_append_include_sql(sql, constraint)
1268 end

column_definition_append_unique_sql(sql, constraint) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1270 def column_definition_append_unique_sql(sql, constraint)
1271   super
1272   column_definition_append_include_sql(sql, constraint)
1273 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
1277 def column_definition_collate_sql(sql, column)
1278   if collate = column[:collate]
1279     collate = literal(collate) unless collate.is_a?(String)
1280     sql << " COLLATE #{collate}"
1281   end
1282 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
1286 def column_definition_default_sql(sql, column)
1287   super
1288   if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
1289     if (identity = column[:identity])
1290       sql << " GENERATED "
1291       sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
1292       sql << " AS IDENTITY"
1293     elsif (generated = column[:generated_always_as])
1294       sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED"
1295     end
1296   end
1297 end

column_definition_null_sql(sql, column) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1412 def column_definition_null_sql(sql, column)
1413   constraint = column[:not_null]
1414   constraint = nil unless constraint.is_a?(Hash)
1415   if constraint && (name = constraint[:name])
1416     sql << " CONSTRAINT #{quote_identifier(name)}"
1417   end
1418   super
1419   if constraint && constraint[:no_inherit]
1420     sql << " NO INHERIT"
1421   end
1422 end

column_references_add_period(cols) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1439 def column_references_add_period(cols)
1440   cols= cols.dup
1441   cols[-1] = Sequel.lit("PERIOD #{quote_identifier(cols[-1])}")
1442   cols
1443 end

column_references_append_key_sql(sql, column) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1433 def column_references_append_key_sql(sql, column)
1434   cols = Array(column[:key])
1435   cols = column_references_add_period(cols) if column[:period]
1436   sql << "(#{cols.map{|x| quote_identifier(x)}.join(', ')})"
1437 end

column_references_table_constraint_sql(constraint) click to toggle source

Handle :period option

     # File lib/sequel/adapters/shared/postgres.rb
1425 def column_references_table_constraint_sql(constraint)
1426   sql = String.new
1427   sql << "FOREIGN KEY "
1428   cols = constraint[:columns]
1429   cols = column_references_add_period(cols) if constraint[:period]
1430   sql << literal(cols) << column_references_sql(constraint)
1431 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
1300 def column_schema_normalize_default(default, type)
1301   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
1302     default = m[1] || m[2]
1303   end
1304   super(default, type)
1305 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
1319 def combinable_alter_table_op?(op)
1320   (super || op[:op] == :validate_constraint || op[:op] == :alter_constraint) && op[:op] != :rename_column
1321 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
1309 def commit_transaction(conn, opts=OPTS)
1310   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
1311     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
1312   else
1313     super
1314   end
1315 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
1325 def connection_configuration_sqls(opts=@opts)
1326   sqls = []
1327 
1328   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
1329 
1330   cmm = opts.fetch(:client_min_messages, :warning)
1331   if cmm && !cmm.to_s.empty?
1332     cmm = cmm.to_s.upcase.strip
1333     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
1334       raise Error, "Unsupported client_min_messages setting: #{cmm}"
1335     end
1336     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
1337   end
1338 
1339   if search_path = opts[:search_path]
1340     case search_path
1341     when String
1342       search_path = search_path.split(",").map(&:strip)
1343     when Array
1344       # nil
1345     else
1346       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
1347     end
1348     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
1349   end
1350 
1351   sqls
1352 end

constraint_definition_sql(constraint) click to toggle source

Handle PostgreSQL-specific constraint features.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1355 def constraint_definition_sql(constraint)
1356   case type = constraint[:type]
1357   when :exclude
1358     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
1359     sql = String.new
1360     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
1361     sql << "EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})"
1362     column_definition_append_include_sql(sql, constraint)
1363     sql << " WHERE #{filter_expr(constraint[:where])}" if constraint[:where]
1364     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1365     sql
1366   when :primary_key, :unique
1367     sql = String.new
1368     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
1369 
1370     if type == :primary_key
1371       sql << primary_key_constraint_sql_fragment(constraint)
1372     else
1373       sql << unique_constraint_sql_fragment(constraint)
1374     end
1375 
1376     if using_index = constraint[:using_index]
1377       sql << " USING INDEX " << quote_identifier(using_index)
1378     else
1379       cols = literal(constraint[:columns])
1380       cols.insert(-2, " WITHOUT OVERLAPS") if constraint[:without_overlaps]
1381       sql << " " << cols
1382 
1383       if include_cols = constraint[:include]
1384         sql << " INCLUDE " << literal(Array(include_cols))
1385       end
1386     end
1387 
1388     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1389     sql
1390   else # when :foreign_key, :check
1391     sql = super
1392     if constraint[:no_inherit]
1393       sql << " NO INHERIT"
1394     end
1395     if constraint[:not_enforced]
1396       sql << " NOT ENFORCED"
1397     end
1398     if constraint[:not_valid]
1399       sql << " NOT VALID"
1400     end
1401     sql
1402   end
1403 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
1474 def copy_into_sql(table, opts)
1475   sql = String.new
1476   sql << "COPY #{literal(table)}"
1477   if cols = opts[:columns]
1478     sql << literal(Array(cols))
1479   end
1480   sql << " FROM STDIN"
1481   if opts[:options] || opts[:format]
1482     sql << " ("
1483     sql << "FORMAT #{opts[:format]}" if opts[:format]
1484     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1485     sql << ')'
1486   end
1487   sql
1488 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
1491 def copy_table_sql(table, opts)
1492   if table.is_a?(String)
1493     table
1494   else
1495     if opts[:options] || opts[:format]
1496       options = String.new
1497       options << " ("
1498       options << "FORMAT #{opts[:format]}" if opts[:format]
1499       options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1500       options << ')'
1501     end
1502     table = if table.is_a?(::Sequel::Dataset)
1503       "(#{table.sql})"
1504     else
1505       literal(table)
1506     end
1507     "COPY #{table} TO STDOUT#{options}"
1508   end
1509 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
1512       def create_function_sql(name, definition, opts=OPTS)
1513         args = opts[:args]
1514         in_out = %w'OUT INOUT'
1515         if (!opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 && in_out.include?(a[2].to_s)})
1516           returns = opts[:returns] || 'void'
1517         end
1518         language = opts[:language] || 'SQL'
1519         <<-END
1520         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
1521         #{"RETURNS #{returns}" if returns}
1522         LANGUAGE #{language}
1523         #{opts[:behavior].to_s.upcase if opts[:behavior]}
1524         #{'STRICT' if opts[:strict]}
1525         #{'SECURITY DEFINER' if opts[:security_definer]}
1526         #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]}
1527         #{"COST #{opts[:cost]}" if opts[:cost]}
1528         #{"ROWS #{opts[:rows]}" if opts[:rows]}
1529         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
1530         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
1531         END
1532       end

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

SQL for creating a procedural language.

     # File lib/sequel/adapters/shared/postgres.rb
1535 def create_language_sql(name, opts=OPTS)
1536   "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]}"
1537 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
1541 def create_partition_of_table_from_generator(name, generator, options)
1542   execute_ddl(create_partition_of_table_sql(name, generator, options))
1543 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
1546 def create_partition_of_table_sql(name, generator, options)
1547   sql = create_table_prefix_sql(name, options).dup
1548 
1549   sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}"
1550 
1551   case generator.partition_type
1552   when :range
1553     from, to = generator.range
1554     sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}"
1555   when :list
1556     sql << " FOR VALUES IN #{literal(generator.list)}"
1557   when :hash
1558     mod, remainder = generator.hash_values
1559     sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})"
1560   else # when :default
1561     sql << " DEFAULT"
1562   end
1563 
1564   sql << create_table_suffix_sql(name, options)
1565 
1566   sql
1567 end

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

SQL for creating a schema.

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

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

     # File lib/sequel/adapters/shared/postgres.rb
1628 def create_table_as_sql(name, sql, options)
1629   result = create_table_prefix_sql name, options
1630   if on_commit = options[:on_commit]
1631     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
1632   end
1633   result += " AS #{sql}"
1634 end

create_table_generator_class() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1636 def create_table_generator_class
1637   Postgres::CreateTableGenerator
1638 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
1575 def create_table_prefix_sql(name, options)
1576   prefix_sql = if options[:temp]
1577     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
1578     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
1579     temporary_table_sql
1580   elsif options[:foreign]
1581     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
1582     'FOREIGN '
1583   elsif options.fetch(:unlogged){typecast_value_boolean(@opts[:unlogged_tables_default])}
1584     'UNLOGGED '
1585   end
1586 
1587   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{create_table_table_name_sql(name, options)}"
1588 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
1591 def create_table_sql(name, generator, options)
1592   "#{super}#{create_table_suffix_sql(name, options)}"
1593 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
1597 def create_table_suffix_sql(name, options)
1598   sql = String.new
1599 
1600   if inherits = options[:inherits]
1601     sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
1602   end
1603 
1604   if partition_by = options[:partition_by]
1605     sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
1606   end
1607 
1608   if on_commit = options[:on_commit]
1609     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
1610     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
1611     sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
1612   end
1613 
1614   if tablespace = options[:tablespace]
1615     sql << " TABLESPACE #{quote_identifier(tablespace)}"
1616   end
1617 
1618   if server = options[:foreign]
1619     sql << " SERVER #{quote_identifier(server)}"
1620     if foreign_opts = options[:options]
1621       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
1622     end
1623   end
1624 
1625   sql
1626 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
1641 def create_trigger_sql(table, name, function, opts=OPTS)
1642   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
1643   whence = opts[:after] ? 'AFTER' : 'BEFORE'
1644   if filter = opts[:when]
1645     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
1646     filter = " WHEN #{filter_expr(filter)}"
1647   end
1648   "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(', ')})"
1649 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
1652 def create_view_prefix_sql(name, options)
1653   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])
1654 
1655   if options[:security_invoker]
1656     sql += " WITH (security_invoker)"
1657   end
1658 
1659   if tablespace = options[:tablespace]
1660     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1661   end
1662 
1663   sql
1664 end

database_error_regexps() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1469 def database_error_regexps
1470   DATABASE_ERROR_REGEXPS
1471 end

database_specific_error_class_from_sqlstate(sqlstate) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1445 def database_specific_error_class_from_sqlstate(sqlstate)
1446   if sqlstate == '23P01'
1447     ExclusionConstraintViolation
1448   elsif sqlstate == '40P01'
1449     SerializationFailure
1450   elsif sqlstate == '55P03'
1451     DatabaseLockTimeout
1452   else
1453     super
1454   end
1455 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
1667 def drop_function_sql(name, opts=OPTS)
1668   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
1669 end

drop_index_sql(table, op) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1672 def drop_index_sql(table, op)
1673   sch, _ = schema_and_table(table)
1674   "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]}"
1675 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
1678 def drop_language_sql(name, opts=OPTS)
1679   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1680 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
1683 def drop_schema_sql(name, opts=OPTS)
1684   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1685 end

drop_table_sql(name, options) click to toggle source

Support :foreign tables

     # File lib/sequel/adapters/shared/postgres.rb
1693 def drop_table_sql(name, options)
1694   "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
1695 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
1688 def drop_trigger_sql(table, name, opts=OPTS)
1689   "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
1690 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
1698 def drop_view_sql(name, opts=OPTS)
1699   "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
1700 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
1704 def filter_schema(ds, opts)
1705   expr = if schema = opts[:schema]
1706     if schema.is_a?(SQL::Identifier)
1707       schema.value.to_s
1708     else
1709       schema.to_s
1710     end
1711   else
1712     Sequel.function(:any, Sequel.function(:current_schemas, false))
1713   end
1714   ds.where{{pg_namespace[:nspname]=>expr}}
1715 end

index_definition_sql(table_name, index) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1717 def index_definition_sql(table_name, index)
1718   cols = index[:columns]
1719   index_name = index[:name] || default_index_name(table_name, cols)
1720 
1721   expr = if o = index[:opclass]
1722     "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
1723   else
1724     literal(Array(cols))
1725   end
1726 
1727   if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
1728   unique = "UNIQUE " if index[:unique]
1729   index_type = index[:type]
1730   filter = index[:where] || index[:filter]
1731   filter = " WHERE #{filter_expr(filter)}" if filter
1732   nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil?
1733 
1734   case index_type
1735   when :full_text
1736     expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
1737     index_type = index[:index_type] || :gin
1738   when :spatial
1739     index_type = :gist
1740   end
1741 
1742   "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}"
1743 end

initialize_postgres_adapter() click to toggle source

Setup datastructures shared by all postgres adapters.

     # File lib/sequel/adapters/shared/postgres.rb
1746 def initialize_postgres_adapter
1747   @primary_keys = {}
1748   @primary_key_sequences = {}
1749   @supported_types = {}
1750   procs = @conversion_procs = CONVERSION_PROCS.dup
1751   procs[1184] = procs[1114] = method(:to_application_timestamp)
1752 end

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

Backbone of the tables and views support.

     # File lib/sequel/adapters/shared/postgres.rb
1755 def pg_class_relname(type, opts)
1756   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
1757   ds = filter_schema(ds, opts)
1758   m = output_identifier_meth
1759   if defined?(yield)
1760     yield(ds)
1761   elsif opts[:qualify]
1762     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
1763   else
1764     ds.map{|r| m.call(r[:relname])}
1765   end
1766 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
1770 def regclass_oid(expr, opts=OPTS)
1771   if expr.is_a?(String) && !expr.is_a?(LiteralString)
1772     expr = Sequel.identifier(expr)
1773   end
1774 
1775   sch, table = schema_and_table(expr)
1776   sch ||= opts[:schema]
1777   if sch
1778     expr = Sequel.qualify(sch, table)
1779   end
1780   
1781   expr = if ds = opts[:dataset]
1782     ds.literal(expr)
1783   else
1784     literal(expr)
1785   end
1786 
1787   Sequel.cast(expr.to_s,:regclass).cast(:oid)
1788 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
1791 def remove_cached_schema(table)
1792   tab = quote_schema_table(table)
1793   Sequel.synchronize do
1794     @primary_keys.delete(tab)
1795     @primary_key_sequences.delete(tab)
1796   end
1797   super
1798 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
1802 def rename_table_sql(name, new_name)
1803   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
1804 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
1819 def schema_array_type(db_type)
1820   :array
1821 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
1807 def schema_column_type(db_type)
1808   case db_type
1809   when /\Ainterval\z/i
1810     :interval
1811   when /\Acitext\z/i
1812     :string
1813   else
1814     super
1815   end
1816 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
1824 def schema_composite_type(db_type)
1825   :composite
1826 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
1829 def schema_enum_type(db_type)
1830   :enum
1831 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
1839 def schema_multirange_type(db_type)
1840   :multirange
1841 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
1856 def schema_parse_table(table_name, opts)
1857   m = output_identifier_meth(opts[:dataset])
1858 
1859   _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row|
1860     row[:default] = nil if blank_object?(row[:default])
1861     if row[:base_oid]
1862       row[:domain_oid] = row[:oid]
1863       row[:oid] = row.delete(:base_oid)
1864       row[:db_domain_type] = row[:db_type]
1865       row[:db_type] = row.delete(:db_base_type)
1866     else
1867       row.delete(:base_oid)
1868       row.delete(:db_base_type)
1869     end
1870 
1871     db_type = row[:db_type]
1872     row[:type] = if row.delete(:is_array)
1873       schema_array_type(db_type)
1874     else
1875       send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type)
1876     end
1877     identity = row.delete(:attidentity)
1878     if row[:primary_key]
1879       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
1880     end
1881 
1882     # :nocov:
1883     if server_version >= 90600
1884     # :nocov:
1885       case row[:oid]
1886       when 1082
1887         row[:min_value] = MIN_DATE
1888         row[:max_value] = MAX_DATE
1889       when 1184, 1114
1890         if Sequel.datetime_class == Time
1891           row[:min_value] = MIN_TIMESTAMP
1892           row[:max_value] = MAX_TIMESTAMP
1893         end
1894       end
1895     end
1896 
1897     [m.call(row.delete(:name)), row]
1898   end
1899 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
1834 def schema_range_type(db_type)
1835   :range
1836 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
1902 def set_transaction_isolation(conn, opts)
1903   level = opts.fetch(:isolation, transaction_isolation_level)
1904   read_only = opts[:read_only]
1905   deferrable = opts[:deferrable]
1906   if level || !read_only.nil? || !deferrable.nil?
1907     sql = String.new
1908     sql << "SET TRANSACTION"
1909     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
1910     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
1911     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
1912     log_connection_execute(conn, sql)
1913   end
1914 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
1917 def sql_function_args(args)
1918   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
1919 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
1922 def supports_combining_alter_table_ops?
1923   true
1924 end

supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1927 def supports_create_or_replace_view?
1928   true
1929 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
1932 def type_literal_generic_bignum_symbol(column)
1933   column[:serial] ? :bigserial : super
1934 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
1937 def type_literal_generic_file(column)
1938   :bytea
1939 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
1942 def type_literal_generic_integer(column)
1943   column[:serial] ? :serial : super
1944 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
1950 def type_literal_generic_string(column)
1951   if column[:text]
1952     :text
1953   elsif column[:fixed]
1954     "char(#{column[:size]||default_string_column_size})"
1955   elsif column[:text] == false || column[:size]
1956     "varchar(#{column[:size]||default_string_column_size})"
1957   else
1958     :text
1959   end
1960 end

unique_constraint_sql_fragment(constraint) click to toggle source

Support :nulls_not_distinct option.

     # File lib/sequel/adapters/shared/postgres.rb
1963 def unique_constraint_sql_fragment(constraint)
1964   if constraint[:nulls_not_distinct]
1965     'UNIQUE NULLS NOT DISTINCT'
1966   else
1967     'UNIQUE'
1968   end
1969 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
1972 def view_with_check_option_support
1973   # :nocov:
1974   :local if server_version >= 90400
1975   # :nocov:
1976 end