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
320 def add_conversion_proc(oid, callable=nil, &block)
321   conversion_procs[oid] = callable || block
322 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
327 def add_named_conversion_proc(name, &block)
328   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
329     raise Error, "No matching type in pg_type for #{name.inspect}"
330   end
331   add_conversion_proc(oid, block)
332 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
343 def check_constraints(table)
344   m = output_identifier_meth
345 
346   hash = {}
347   _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row|
348     constraint = m.call(row[:constraint])
349     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[], :validated=>row[:validated], :enforced=>row[:enforced]}
350     entry[:columns] << m.call(row[:column]) if row[:column]
351   end
352   
353   hash
354 end
commit_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
334 def commit_prepared_transaction(transaction_id, opts=OPTS)
335   run("COMMIT PREPARED #{literal(transaction_id)}", opts)
336 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
374 def convert_serial_to_identity(table, opts=OPTS)
375   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
376 
377   server = opts[:server]
378   server_hash = server ? {:server=>server} : OPTS
379   ds = dataset
380   ds = ds.server(server) if server
381 
382   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
383 
384   table_oid = regclass_oid(table)
385   im = input_identifier_meth
386   unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
387     raise Error, "could not determine column to convert from serial to identity automatically"
388   end
389   column = im.call(column)
390 
391   column_num = ds.from(:pg_attribute).
392     where(:attrelid=>table_oid, :attname=>column).
393     get(:attnum)
394 
395   pg_class = Sequel.cast('pg_class', :regclass)
396   res = ds.from(:pg_depend).
397     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
398     select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])
399 
400   case res.length
401   when 0
402     raise Error, "unable to find related sequence when converting serial to identity"
403   when 1
404     seq_oid, already_identity = res.first
405   else
406     raise Error, "more than one linked sequence found when converting serial to identity"
407   end
408 
409   return if already_identity
410 
411   transaction(server_hash) do
412     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash)
413 
414     ds.from(:pg_depend).
415       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
416       update(:deptype=>'i')
417 
418     ds.from(:pg_attribute).
419       where(:attrelid=>table_oid, :attname=>column).
420       update(:attidentity=>'d')
421   end
422 
423   remove_cached_schema(table)
424   nil
425 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
448 def create_function(name, definition, opts=OPTS)
449   self << create_function_sql(name, definition, opts)
450 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
459 def create_language(name, opts=OPTS)
460   self << create_language_sql(name, opts)
461 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
468 def create_schema(name, opts=OPTS)
469   self << create_schema_sql(name, opts)
470 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
473 def create_table(name, options=OPTS, &block)
474   if options[:partition_of]
475     create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
476     return
477   end
478 
479   super
480 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
483 def create_table?(name, options=OPTS, &block)
484   if options[:partition_of]
485     create_table(name, options.merge!(:if_not_exists=>true), &block)
486     return
487   end
488 
489   super
490 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
504 def create_trigger(table, name, function, opts=OPTS)
505   self << create_trigger_sql(table, name, function, opts)
506 end
database_type() click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
508 def database_type
509   :postgres
510 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
527 def defer_constraints(opts=OPTS)
528   _set_constraints(' DEFERRED', opts)
529 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
536 def do(code, opts=OPTS)
537   language = opts[:language]
538   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}"
539 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
547 def drop_function(name, opts=OPTS)
548   self << drop_function_sql(name, opts)
549 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
556 def drop_language(name, opts=OPTS)
557   self << drop_language_sql(name, opts)
558 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
565 def drop_schema(name, opts=OPTS)
566   self << drop_schema_sql(name, opts)
567 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
575 def drop_trigger(table, name, opts=OPTS)
576   self << drop_trigger_sql(table, name, opts)
577 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
589 def foreign_key_list(table, opts=OPTS)
590   m = output_identifier_meth
591   schema, _ = opts.fetch(:schema, schema_and_table(table))
592 
593   h = {}
594   fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
595   reverse = opts[:reverse]
596 
597   (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row|
598     if reverse
599       key = [row[:schema], row[:table], row[:name]]
600     else
601       key = row[:name]
602     end
603 
604     if r = h[key]
605       r[:columns] << m.call(row[:column])
606       r[:key] << m.call(row[:refcolumn])
607     else
608       entry = h[key] = {
609         :name=>m.call(row[:name]),
610         :columns=>[m.call(row[:column])],
611         :key=>[m.call(row[:refcolumn])],
612         :on_update=>fklod_map[row[:on_update]],
613         :on_delete=>fklod_map[row[:on_delete]],
614         :deferrable=>row[:deferrable],
615         :validated=>row[:validated],
616         :enforced=>row[:enforced],
617         :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
618       }
619 
620       unless schema
621         # If not combining schema information into the :table entry
622         # include it as a separate entry.
623         entry[:schema] = m.call(row[:schema])
624       end
625     end
626   end
627 
628   h.values
629 end
freeze() click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/postgres.rb
631 def freeze
632   server_version
633   supports_prepared_transactions?
634   _schema_ds
635   _select_serial_sequence_ds
636   _select_custom_sequence_ds
637   _select_pk_ds
638   _indexes_ds
639   _check_constraints_ds
640   _foreign_key_list_ds
641   _reverse_foreign_key_list_ds
642   @conversion_procs.freeze
643   super
644 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
660 def immediate_constraints(opts=OPTS)
661   _set_constraints(' IMMEDIATE', opts)
662 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
665 def indexes(table, opts=OPTS)
666   m = output_identifier_meth
667   cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)}
668   cond[:indpred] = nil unless opts[:include_partial]
669 
670   indexes = {}
671   _indexes_ds.where_each(cond) do |r|
672     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
673     i[:columns] << m.call(r[:column])
674   end
675   indexes
676 end
locks() click to toggle source

Dataset containing all current database locks

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

Return primary key for the given table.

    # File lib/sequel/adapters/shared/postgres.rb
701 def primary_key(table, opts=OPTS)
702   quoted_table = quote_schema_table(table)
703   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
704   value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts))
705   Sequel.synchronize{@primary_keys[quoted_table] = value}
706 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
709 def primary_key_sequence(table, opts=OPTS)
710   quoted_table = quote_schema_table(table)
711   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
712   cond = {Sequel[:t][:oid] => regclass_oid(table, opts)}
713   value = if pks = _select_serial_sequence_ds.first(cond)
714     literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
715   elsif pks = _select_custom_sequence_ds.first(cond)
716     literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
717   end
718 
719   Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value
720 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
728 def refresh_view(name, opts=OPTS)
729   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
730 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
734 def reset_primary_key_sequence(table)
735   return unless seq = primary_key_sequence(table)
736   pk = SQL::Identifier.new(primary_key(table))
737   db = self
738   s, t = schema_and_table(table)
739   table = Sequel.qualify(s, t) if s
740 
741   if server_version >= 100000
742     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq)))
743     increment_by = :seqincrement
744     min_value = :seqmin
745   # :nocov:
746   else
747     seq_ds = metadata_dataset.from(LiteralString.new(seq))
748     increment_by = :increment_by
749     min_value = :min_value
750   # :nocov:
751   end
752 
753   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
754 end
rollback_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
756 def rollback_prepared_transaction(transaction_id, opts=OPTS)
757   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
758 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
762 def serial_primary_key_options
763   # :nocov:
764   auto_increment_key = server_version >= 100002 ? :identity : :serial
765   # :nocov:
766   {:primary_key => true, auto_increment_key => true, :type=>Integer}
767 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
770 def server_version(server=nil)
771   return @server_version if @server_version
772   ds = dataset
773   ds = ds.server(server) if server
774   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
775 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
778 def supports_create_table_if_not_exists?
779   server_version >= 90100
780 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
783 def supports_deferrable_constraints?
784   server_version >= 90000
785 end
supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

    # File lib/sequel/adapters/shared/postgres.rb
788 def supports_deferrable_foreign_key_constraints?
789   true
790 end
supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

    # File lib/sequel/adapters/shared/postgres.rb
793 def supports_drop_table_if_exists?
794   true
795 end
supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

    # File lib/sequel/adapters/shared/postgres.rb
798 def supports_partial_indexes?
799   true
800 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
809 def supports_prepared_transactions?
810   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
811   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
812 end
supports_savepoints?() click to toggle source

PostgreSQL supports savepoints

    # File lib/sequel/adapters/shared/postgres.rb
815 def supports_savepoints?
816   true
817 end
supports_transaction_isolation_levels?() click to toggle source

PostgreSQL supports transaction isolation levels

    # File lib/sequel/adapters/shared/postgres.rb
820 def supports_transaction_isolation_levels?
821   true
822 end
supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

    # File lib/sequel/adapters/shared/postgres.rb
825 def supports_transactional_ddl?
826   true
827 end
supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

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

Dataset used to retrieve CHECK constraint information

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

Dataset used to retrieve index information

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

Dataset used to get schema for tables

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

Dataset used to determine primary keys for tables

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

Internals of defer_constraints/immediate_constraints

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

SQL to use for SET CONSTRAINTS

     # File lib/sequel/adapters/shared/postgres.rb
1159 def _set_constraints_sql(type, opts)
1160   sql = String.new
1161   sql << "SET CONSTRAINTS "
1162   if constraints = opts[:constraints]
1163     dataset.send(:source_list_append, sql, Array(constraints))
1164   else
1165     sql << "ALL"
1166   end
1167   sql << type
1168 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
1172 def _table_exists?(ds)
1173   super
1174 rescue DatabaseError => e    
1175   raise e unless /canceling statement due to (?:statement|lock) timeout/ =~ e.message 
1176 end
alter_table_add_column_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1178 def alter_table_add_column_sql(table, op)
1179   "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}"
1180 end
alter_table_alter_constraint_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1182 def alter_table_alter_constraint_sql(table, op)
1183   sql = String.new
1184   sql << "ALTER CONSTRAINT #{quote_identifier(op[:name])}"
1185   
1186   constraint_deferrable_sql_append(sql, op[:deferrable])
1187 
1188   case op[:enforced]
1189   when nil
1190   when false
1191     sql << " NOT ENFORCED"
1192   else
1193     sql << " ENFORCED"
1194   end
1195 
1196   case op[:inherit]
1197   when nil
1198   when false
1199     sql << " NO INHERIT"
1200   else
1201     sql << " INHERIT"
1202   end
1203 
1204   sql
1205 end
alter_table_drop_column_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1221 def alter_table_drop_column_sql(table, op)
1222   "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}"
1223 end
alter_table_generator_class() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1207 def alter_table_generator_class
1208   Postgres::AlterTableGenerator
1209 end
alter_table_set_column_type_sql(table, op) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1211 def alter_table_set_column_type_sql(table, op)
1212   s = super
1213   if using = op[:using]
1214     using = Sequel::LiteralString.new(using) if using.is_a?(String)
1215     s += ' USING '
1216     s << literal(using)
1217   end
1218   s
1219 end
alter_table_validate_constraint_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1225 def alter_table_validate_constraint_sql(table, op)
1226   "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
1227 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
1232 def begin_new_transaction(conn, opts)
1233   super
1234   if opts.has_key?(:synchronous)
1235     case sync = opts[:synchronous]
1236     when true
1237       sync = :on
1238     when false
1239       sync = :off
1240     when nil
1241       return
1242     end
1243 
1244     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
1245   end
1246 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
1249 def begin_savepoint(conn, opts)
1250   super
1251 
1252   unless (read_only = opts[:read_only]).nil?
1253     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
1254   end
1255 end
column_definition_add_references_sql(sql, column) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1370 def column_definition_add_references_sql(sql, column)
1371   super
1372   if column[:not_enforced]
1373     sql << " NOT ENFORCED"
1374   end
1375 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
1259 def column_definition_collate_sql(sql, column)
1260   if collate = column[:collate]
1261     collate = literal(collate) unless collate.is_a?(String)
1262     sql << " COLLATE #{collate}"
1263   end
1264 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
1268 def column_definition_default_sql(sql, column)
1269   super
1270   if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
1271     if (identity = column[:identity])
1272       sql << " GENERATED "
1273       sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
1274       sql << " AS IDENTITY"
1275     elsif (generated = column[:generated_always_as])
1276       sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED"
1277     end
1278   end
1279 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
1282 def column_schema_normalize_default(default, type)
1283   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
1284     default = m[1] || m[2]
1285   end
1286   super(default, type)
1287 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
1301 def combinable_alter_table_op?(op)
1302   (super || op[:op] == :validate_constraint || op[:op] == :alter_constraint) && op[:op] != :rename_column
1303 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
1291 def commit_transaction(conn, opts=OPTS)
1292   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
1293     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
1294   else
1295     super
1296   end
1297 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
1307 def connection_configuration_sqls(opts=@opts)
1308   sqls = []
1309 
1310   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
1311 
1312   cmm = opts.fetch(:client_min_messages, :warning)
1313   if cmm && !cmm.to_s.empty?
1314     cmm = cmm.to_s.upcase.strip
1315     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
1316       raise Error, "Unsupported client_min_messages setting: #{cmm}"
1317     end
1318     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
1319   end
1320 
1321   if search_path = opts[:search_path]
1322     case search_path
1323     when String
1324       search_path = search_path.split(",").map(&:strip)
1325     when Array
1326       # nil
1327     else
1328       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
1329     end
1330     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
1331   end
1332 
1333   sqls
1334 end
constraint_definition_sql(constraint) click to toggle source

Handle PostgreSQL-specific constraint features.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1337 def constraint_definition_sql(constraint)
1338   case type = constraint[:type]
1339   when :exclude
1340     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
1341     sql = String.new
1342     sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}"
1343     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1344     sql
1345   when :primary_key, :unique
1346     if using_index = constraint[:using_index]
1347       sql = String.new
1348       sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
1349       if type == :primary_key
1350         sql << primary_key_constraint_sql_fragment(constraint)
1351       else
1352         sql << unique_constraint_sql_fragment(constraint)
1353       end
1354       sql << " USING INDEX " << quote_identifier(using_index)
1355     else
1356       super
1357     end
1358   else # when :foreign_key, :check
1359     sql = super
1360     if constraint[:not_enforced]
1361       sql << " NOT ENFORCED"
1362     end
1363     if constraint[:not_valid]
1364       sql << " NOT VALID"
1365     end
1366     sql
1367   end
1368 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
1406 def copy_into_sql(table, opts)
1407   sql = String.new
1408   sql << "COPY #{literal(table)}"
1409   if cols = opts[:columns]
1410     sql << literal(Array(cols))
1411   end
1412   sql << " FROM STDIN"
1413   if opts[:options] || opts[:format]
1414     sql << " ("
1415     sql << "FORMAT #{opts[:format]}" if opts[:format]
1416     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1417     sql << ')'
1418   end
1419   sql
1420 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
1423 def copy_table_sql(table, opts)
1424   if table.is_a?(String)
1425     table
1426   else
1427     if opts[:options] || opts[:format]
1428       options = String.new
1429       options << " ("
1430       options << "FORMAT #{opts[:format]}" if opts[:format]
1431       options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1432       options << ')'
1433     end
1434     table = if table.is_a?(::Sequel::Dataset)
1435       "(#{table.sql})"
1436     else
1437       literal(table)
1438     end
1439     "COPY #{table} TO STDOUT#{options}"
1440   end
1441 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
1444       def create_function_sql(name, definition, opts=OPTS)
1445         args = opts[:args]
1446         in_out = %w'OUT INOUT'
1447         if (!opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 && in_out.include?(a[2].to_s)})
1448           returns = opts[:returns] || 'void'
1449         end
1450         language = opts[:language] || 'SQL'
1451         <<-END
1452         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
1453         #{"RETURNS #{returns}" if returns}
1454         LANGUAGE #{language}
1455         #{opts[:behavior].to_s.upcase if opts[:behavior]}
1456         #{'STRICT' if opts[:strict]}
1457         #{'SECURITY DEFINER' if opts[:security_definer]}
1458         #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]}
1459         #{"COST #{opts[:cost]}" if opts[:cost]}
1460         #{"ROWS #{opts[:rows]}" if opts[:rows]}
1461         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
1462         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
1463         END
1464       end
create_language_sql(name, opts=OPTS) click to toggle source

SQL for creating a procedural language.

     # File lib/sequel/adapters/shared/postgres.rb
1467 def create_language_sql(name, opts=OPTS)
1468   "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]}"
1469 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
1473 def create_partition_of_table_from_generator(name, generator, options)
1474   execute_ddl(create_partition_of_table_sql(name, generator, options))
1475 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
1478 def create_partition_of_table_sql(name, generator, options)
1479   sql = create_table_prefix_sql(name, options).dup
1480 
1481   sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}"
1482 
1483   case generator.partition_type
1484   when :range
1485     from, to = generator.range
1486     sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}"
1487   when :list
1488     sql << " FOR VALUES IN #{literal(generator.list)}"
1489   when :hash
1490     mod, remainder = generator.hash_values
1491     sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})"
1492   else # when :default
1493     sql << " DEFAULT"
1494   end
1495 
1496   sql << create_table_suffix_sql(name, options)
1497 
1498   sql
1499 end
create_schema_sql(name, opts=OPTS) click to toggle source

SQL for creating a schema.

     # File lib/sequel/adapters/shared/postgres.rb
1502 def create_schema_sql(name, opts=OPTS)
1503   "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}"
1504 end
create_table_as_sql(name, sql, options) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1560 def create_table_as_sql(name, sql, options)
1561   result = create_table_prefix_sql name, options
1562   if on_commit = options[:on_commit]
1563     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
1564   end
1565   result += " AS #{sql}"
1566 end
create_table_generator_class() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1568 def create_table_generator_class
1569   Postgres::CreateTableGenerator
1570 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
1507 def create_table_prefix_sql(name, options)
1508   prefix_sql = if options[:temp]
1509     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
1510     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
1511     temporary_table_sql
1512   elsif options[:foreign]
1513     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
1514     'FOREIGN '
1515   elsif options.fetch(:unlogged){typecast_value_boolean(@opts[:unlogged_tables_default])}
1516     'UNLOGGED '
1517   end
1518 
1519   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{create_table_table_name_sql(name, options)}"
1520 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
1523 def create_table_sql(name, generator, options)
1524   "#{super}#{create_table_suffix_sql(name, options)}"
1525 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
1529 def create_table_suffix_sql(name, options)
1530   sql = String.new
1531 
1532   if inherits = options[:inherits]
1533     sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
1534   end
1535 
1536   if partition_by = options[:partition_by]
1537     sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
1538   end
1539 
1540   if on_commit = options[:on_commit]
1541     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
1542     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
1543     sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
1544   end
1545 
1546   if tablespace = options[:tablespace]
1547     sql << " TABLESPACE #{quote_identifier(tablespace)}"
1548   end
1549 
1550   if server = options[:foreign]
1551     sql << " SERVER #{quote_identifier(server)}"
1552     if foreign_opts = options[:options]
1553       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
1554     end
1555   end
1556 
1557   sql
1558 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
1573 def create_trigger_sql(table, name, function, opts=OPTS)
1574   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
1575   whence = opts[:after] ? 'AFTER' : 'BEFORE'
1576   if filter = opts[:when]
1577     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
1578     filter = " WHEN #{filter_expr(filter)}"
1579   end
1580   "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(', ')})"
1581 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
1584 def create_view_prefix_sql(name, options)
1585   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])
1586 
1587   if options[:security_invoker]
1588     sql += " WITH (security_invoker)"
1589   end
1590 
1591   if tablespace = options[:tablespace]
1592     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1593   end
1594 
1595   sql
1596 end
database_error_regexps() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1401 def database_error_regexps
1402   DATABASE_ERROR_REGEXPS
1403 end
database_specific_error_class_from_sqlstate(sqlstate) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1377 def database_specific_error_class_from_sqlstate(sqlstate)
1378   if sqlstate == '23P01'
1379     ExclusionConstraintViolation
1380   elsif sqlstate == '40P01'
1381     SerializationFailure
1382   elsif sqlstate == '55P03'
1383     DatabaseLockTimeout
1384   else
1385     super
1386   end
1387 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
1599 def drop_function_sql(name, opts=OPTS)
1600   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
1601 end
drop_index_sql(table, op) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1604 def drop_index_sql(table, op)
1605   sch, _ = schema_and_table(table)
1606   "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]}"
1607 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
1610 def drop_language_sql(name, opts=OPTS)
1611   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1612 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
1615 def drop_schema_sql(name, opts=OPTS)
1616   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1617 end
drop_table_sql(name, options) click to toggle source

Support :foreign tables

     # File lib/sequel/adapters/shared/postgres.rb
1625 def drop_table_sql(name, options)
1626   "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
1627 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
1620 def drop_trigger_sql(table, name, opts=OPTS)
1621   "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
1622 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
1630 def drop_view_sql(name, opts=OPTS)
1631   "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
1632 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
1636 def filter_schema(ds, opts)
1637   expr = if schema = opts[:schema]
1638     if schema.is_a?(SQL::Identifier)
1639       schema.value.to_s
1640     else
1641       schema.to_s
1642     end
1643   else
1644     Sequel.function(:any, Sequel.function(:current_schemas, false))
1645   end
1646   ds.where{{pg_namespace[:nspname]=>expr}}
1647 end
index_definition_sql(table_name, index) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1649 def index_definition_sql(table_name, index)
1650   cols = index[:columns]
1651   index_name = index[:name] || default_index_name(table_name, cols)
1652 
1653   expr = if o = index[:opclass]
1654     "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
1655   else
1656     literal(Array(cols))
1657   end
1658 
1659   if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
1660   unique = "UNIQUE " if index[:unique]
1661   index_type = index[:type]
1662   filter = index[:where] || index[:filter]
1663   filter = " WHERE #{filter_expr(filter)}" if filter
1664   nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil?
1665 
1666   case index_type
1667   when :full_text
1668     expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
1669     index_type = index[:index_type] || :gin
1670   when :spatial
1671     index_type = :gist
1672   end
1673 
1674   "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}"
1675 end
initialize_postgres_adapter() click to toggle source

Setup datastructures shared by all postgres adapters.

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

Backbone of the tables and views support.

     # File lib/sequel/adapters/shared/postgres.rb
1687 def pg_class_relname(type, opts)
1688   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
1689   ds = filter_schema(ds, opts)
1690   m = output_identifier_meth
1691   if defined?(yield)
1692     yield(ds)
1693   elsif opts[:qualify]
1694     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
1695   else
1696     ds.map{|r| m.call(r[:relname])}
1697   end
1698 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
1702 def regclass_oid(expr, opts=OPTS)
1703   if expr.is_a?(String) && !expr.is_a?(LiteralString)
1704     expr = Sequel.identifier(expr)
1705   end
1706 
1707   sch, table = schema_and_table(expr)
1708   sch ||= opts[:schema]
1709   if sch
1710     expr = Sequel.qualify(sch, table)
1711   end
1712   
1713   expr = if ds = opts[:dataset]
1714     ds.literal(expr)
1715   else
1716     literal(expr)
1717   end
1718 
1719   Sequel.cast(expr.to_s,:regclass).cast(:oid)
1720 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
1723 def remove_cached_schema(table)
1724   tab = quote_schema_table(table)
1725   Sequel.synchronize do
1726     @primary_keys.delete(tab)
1727     @primary_key_sequences.delete(tab)
1728   end
1729   super
1730 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
1734 def rename_table_sql(name, new_name)
1735   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
1736 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
1751 def schema_array_type(db_type)
1752   :array
1753 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
1739 def schema_column_type(db_type)
1740   case db_type
1741   when /\Ainterval\z/i
1742     :interval
1743   when /\Acitext\z/i
1744     :string
1745   else
1746     super
1747   end
1748 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
1756 def schema_composite_type(db_type)
1757   :composite
1758 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
1761 def schema_enum_type(db_type)
1762   :enum
1763 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
1771 def schema_multirange_type(db_type)
1772   :multirange
1773 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
1788 def schema_parse_table(table_name, opts)
1789   m = output_identifier_meth(opts[:dataset])
1790 
1791   _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row|
1792     row[:default] = nil if blank_object?(row[:default])
1793     if row[:base_oid]
1794       row[:domain_oid] = row[:oid]
1795       row[:oid] = row.delete(:base_oid)
1796       row[:db_domain_type] = row[:db_type]
1797       row[:db_type] = row.delete(:db_base_type)
1798     else
1799       row.delete(:base_oid)
1800       row.delete(:db_base_type)
1801     end
1802 
1803     db_type = row[:db_type]
1804     row[:type] = if row.delete(:is_array)
1805       schema_array_type(db_type)
1806     else
1807       send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type)
1808     end
1809     identity = row.delete(:attidentity)
1810     if row[:primary_key]
1811       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
1812     end
1813 
1814     # :nocov:
1815     if server_version >= 90600
1816     # :nocov:
1817       case row[:oid]
1818       when 1082
1819         row[:min_value] = MIN_DATE
1820         row[:max_value] = MAX_DATE
1821       when 1184, 1114
1822         if Sequel.datetime_class == Time
1823           row[:min_value] = MIN_TIMESTAMP
1824           row[:max_value] = MAX_TIMESTAMP
1825         end
1826       end
1827     end
1828 
1829     [m.call(row.delete(:name)), row]
1830   end
1831 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
1766 def schema_range_type(db_type)
1767   :range
1768 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
1834 def set_transaction_isolation(conn, opts)
1835   level = opts.fetch(:isolation, transaction_isolation_level)
1836   read_only = opts[:read_only]
1837   deferrable = opts[:deferrable]
1838   if level || !read_only.nil? || !deferrable.nil?
1839     sql = String.new
1840     sql << "SET TRANSACTION"
1841     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
1842     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
1843     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
1844     log_connection_execute(conn, sql)
1845   end
1846 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
1849 def sql_function_args(args)
1850   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
1851 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
1854 def supports_combining_alter_table_ops?
1855   true
1856 end
supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1859 def supports_create_or_replace_view?
1860   true
1861 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
1864 def type_literal_generic_bignum_symbol(column)
1865   column[:serial] ? :bigserial : super
1866 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
1869 def type_literal_generic_file(column)
1870   :bytea
1871 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
1874 def type_literal_generic_integer(column)
1875   column[:serial] ? :serial : super
1876 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
1882 def type_literal_generic_string(column)
1883   if column[:text]
1884     :text
1885   elsif column[:fixed]
1886     "char(#{column[:size]||default_string_column_size})"
1887   elsif column[:text] == false || column[:size]
1888     "varchar(#{column[:size]||default_string_column_size})"
1889   else
1890     :text
1891   end
1892 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
1895 def view_with_check_option_support
1896   # :nocov:
1897   :local if server_version >= 90400
1898   # :nocov:
1899 end