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
309 def add_conversion_proc(oid, callable=nil, &block)
310   conversion_procs[oid] = callable || block
311 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
316 def add_named_conversion_proc(name, &block)
317   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
318     raise Error, "No matching type in pg_type for #{name.inspect}"
319   end
320   add_conversion_proc(oid, block)
321 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
332 def check_constraints(table)
333   m = output_identifier_meth
334 
335   hash = {}
336   _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row|
337     constraint = m.call(row[:constraint])
338     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]}
339     entry[:columns] << m.call(row[:column]) if row[:column]
340   end
341   
342   hash
343 end
commit_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
323 def commit_prepared_transaction(transaction_id, opts=OPTS)
324   run("COMMIT PREPARED #{literal(transaction_id)}", opts)
325 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
363 def convert_serial_to_identity(table, opts=OPTS)
364   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
365 
366   server = opts[:server]
367   server_hash = server ? {:server=>server} : OPTS
368   ds = dataset
369   ds = ds.server(server) if server
370 
371   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
372 
373   table_oid = regclass_oid(table)
374   im = input_identifier_meth
375   unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
376     raise Error, "could not determine column to convert from serial to identity automatically"
377   end
378   column = im.call(column)
379 
380   column_num = ds.from(:pg_attribute).
381     where(:attrelid=>table_oid, :attname=>column).
382     get(:attnum)
383 
384   pg_class = Sequel.cast('pg_class', :regclass)
385   res = ds.from(:pg_depend).
386     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
387     select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])
388 
389   case res.length
390   when 0
391     raise Error, "unable to find related sequence when converting serial to identity"
392   when 1
393     seq_oid, already_identity = res.first
394   else
395     raise Error, "more than one linked sequence found when converting serial to identity"
396   end
397 
398   return if already_identity
399 
400   transaction(server_hash) do
401     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash)
402 
403     ds.from(:pg_depend).
404       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
405       update(:deptype=>'i')
406 
407     ds.from(:pg_attribute).
408       where(:attrelid=>table_oid, :attname=>column).
409       update(:attidentity=>'d')
410   end
411 
412   remove_cached_schema(table)
413   nil
414 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
437 def create_function(name, definition, opts=OPTS)
438   self << create_function_sql(name, definition, opts)
439 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
448 def create_language(name, opts=OPTS)
449   self << create_language_sql(name, opts)
450 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
457 def create_schema(name, opts=OPTS)
458   self << create_schema_sql(name, opts)
459 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
462 def create_table(name, options=OPTS, &block)
463   if options[:partition_of]
464     create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
465     return
466   end
467 
468   super
469 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
472 def create_table?(name, options=OPTS, &block)
473   if options[:partition_of]
474     create_table(name, options.merge!(:if_not_exists=>true), &block)
475     return
476   end
477 
478   super
479 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
493 def create_trigger(table, name, function, opts=OPTS)
494   self << create_trigger_sql(table, name, function, opts)
495 end
database_type() click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
497 def database_type
498   :postgres
499 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
506 def do(code, opts=OPTS)
507   language = opts[:language]
508   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}"
509 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
517 def drop_function(name, opts=OPTS)
518   self << drop_function_sql(name, opts)
519 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
526 def drop_language(name, opts=OPTS)
527   self << drop_language_sql(name, opts)
528 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
535 def drop_schema(name, opts=OPTS)
536   self << drop_schema_sql(name, opts)
537 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
545 def drop_trigger(table, name, opts=OPTS)
546   self << drop_trigger_sql(table, name, opts)
547 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
559 def foreign_key_list(table, opts=OPTS)
560   m = output_identifier_meth
561   schema, _ = opts.fetch(:schema, schema_and_table(table))
562 
563   h = {}
564   fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
565   reverse = opts[:reverse]
566 
567   (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row|
568     if reverse
569       key = [row[:schema], row[:table], row[:name]]
570     else
571       key = row[:name]
572     end
573 
574     if r = h[key]
575       r[:columns] << m.call(row[:column])
576       r[:key] << m.call(row[:refcolumn])
577     else
578       entry = h[key] = {
579         :name=>m.call(row[:name]),
580         :columns=>[m.call(row[:column])],
581         :key=>[m.call(row[:refcolumn])],
582         :on_update=>fklod_map[row[:on_update]],
583         :on_delete=>fklod_map[row[:on_delete]],
584         :deferrable=>row[:deferrable],
585         :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
586       }
587 
588       unless schema
589         # If not combining schema information into the :table entry
590         # include it as a separate entry.
591         entry[:schema] = m.call(row[:schema])
592       end
593     end
594   end
595 
596   h.values
597 end
freeze() click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/postgres.rb
599 def freeze
600   server_version
601   supports_prepared_transactions?
602   _schema_ds
603   _select_serial_sequence_ds
604   _select_custom_sequence_ds
605   _select_pk_ds
606   _indexes_ds
607   _check_constraints_ds
608   _foreign_key_list_ds
609   _reverse_foreign_key_list_ds
610   @conversion_procs.freeze
611   super
612 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
615 def indexes(table, opts=OPTS)
616   m = output_identifier_meth
617   cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)}
618   cond[:indpred] = nil unless opts[:include_partial]
619 
620   indexes = {}
621   _indexes_ds.where_each(cond) do |r|
622     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
623     i[:columns] << m.call(r[:column])
624   end
625   indexes
626 end
locks() click to toggle source

Dataset containing all current database locks

    # File lib/sequel/adapters/shared/postgres.rb
629 def locks
630   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
631 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
639 def notify(channel, opts=OPTS)
640   sql = String.new
641   sql << "NOTIFY "
642   dataset.send(:identifier_append, sql, channel)
643   if payload = opts[:payload]
644     sql << ", "
645     dataset.literal_append(sql, payload.to_s)
646   end
647   execute_ddl(sql, opts)
648 end
primary_key(table, opts=OPTS) click to toggle source

Return primary key for the given table.

    # File lib/sequel/adapters/shared/postgres.rb
651 def primary_key(table, opts=OPTS)
652   quoted_table = quote_schema_table(table)
653   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
654   value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts))
655   Sequel.synchronize{@primary_keys[quoted_table] = value}
656 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
659 def primary_key_sequence(table, opts=OPTS)
660   quoted_table = quote_schema_table(table)
661   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
662   cond = {Sequel[:t][:oid] => regclass_oid(table, opts)}
663   value = if pks = _select_serial_sequence_ds.first(cond)
664     literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
665   elsif pks = _select_custom_sequence_ds.first(cond)
666     literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
667   end
668 
669   Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value
670 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
678 def refresh_view(name, opts=OPTS)
679   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
680 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
684 def reset_primary_key_sequence(table)
685   return unless seq = primary_key_sequence(table)
686   pk = SQL::Identifier.new(primary_key(table))
687   db = self
688   s, t = schema_and_table(table)
689   table = Sequel.qualify(s, t) if s
690 
691   if server_version >= 100000
692     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq)))
693     increment_by = :seqincrement
694     min_value = :seqmin
695   # :nocov:
696   else
697     seq_ds = metadata_dataset.from(LiteralString.new(seq))
698     increment_by = :increment_by
699     min_value = :min_value
700   # :nocov:
701   end
702 
703   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
704 end
rollback_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
706 def rollback_prepared_transaction(transaction_id, opts=OPTS)
707   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
708 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
712 def serial_primary_key_options
713   # :nocov:
714   auto_increment_key = server_version >= 100002 ? :identity : :serial
715   # :nocov:
716   {:primary_key => true, auto_increment_key => true, :type=>Integer}
717 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
720 def server_version(server=nil)
721   return @server_version if @server_version
722   ds = dataset
723   ds = ds.server(server) if server
724   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
725 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
728 def supports_create_table_if_not_exists?
729   server_version >= 90100
730 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
733 def supports_deferrable_constraints?
734   server_version >= 90000
735 end
supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

    # File lib/sequel/adapters/shared/postgres.rb
738 def supports_deferrable_foreign_key_constraints?
739   true
740 end
supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

    # File lib/sequel/adapters/shared/postgres.rb
743 def supports_drop_table_if_exists?
744   true
745 end
supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

    # File lib/sequel/adapters/shared/postgres.rb
748 def supports_partial_indexes?
749   true
750 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
759 def supports_prepared_transactions?
760   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
761   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
762 end
supports_savepoints?() click to toggle source

PostgreSQL supports savepoints

    # File lib/sequel/adapters/shared/postgres.rb
765 def supports_savepoints?
766   true
767 end
supports_transaction_isolation_levels?() click to toggle source

PostgreSQL supports transaction isolation levels

    # File lib/sequel/adapters/shared/postgres.rb
770 def supports_transaction_isolation_levels?
771   true
772 end
supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

    # File lib/sequel/adapters/shared/postgres.rb
775 def supports_transactional_ddl?
776   true
777 end
supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
753 def supports_trigger_conditions?
754   server_version >= 90000
755 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
788 def tables(opts=OPTS, &block)
789   pg_class_relname(['r', 'p'], opts, &block)
790 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
794 def type_supported?(type)
795   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
796   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
797   Sequel.synchronize{return @supported_types[type] = supported}
798 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
807 def values(v)
808   raise Error, "Cannot provide an empty array for values" if v.empty?
809   @default_dataset.clone(:values=>v)
810 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
820 def views(opts=OPTS)
821   relkind = opts[:materialized] ? 'm' : 'v'
822   pg_class_relname(relkind, opts)
823 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
847 def __foreign_key_list_ds(reverse)
848   if reverse
849     ctable = Sequel[:att2]
850     cclass = Sequel[:cl2]
851     rtable = Sequel[:att]
852     rclass = Sequel[:cl]
853   else
854     ctable = Sequel[:att]
855     cclass = Sequel[:cl]
856     rtable = Sequel[:att2]
857     rclass = Sequel[:cl2]
858   end
859 
860   if server_version >= 90500
861     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
862     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
863   # :nocov:
864   else
865     range = 0...32
866     cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
867     rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
868   # :nocov:
869   end
870 
871   ds = metadata_dataset.
872     from{pg_constraint.as(:co)}.
873     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
874     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
875     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
876     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
877     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
878     order{[co[:conname], cpos]}.
879     where{{
880       cl[:relkind]=>%w'r p',
881       co[:contype]=>'f',
882       cpos=>rpos
883     }}.
884     select{[
885       co[:conname].as(:name),
886       ctable[:attname].as(:column),
887       co[:confupdtype].as(:on_update),
888       co[:confdeltype].as(:on_delete),
889       cl2[:relname].as(:table),
890       rtable[:attname].as(:refcolumn),
891       SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
892       nsp[:nspname].as(:schema)
893     ]}
894 
895   if reverse
896     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
897   end
898 
899   ds
900 end
_check_constraints_ds() click to toggle source

Dataset used to retrieve CHECK constraint information

    # File lib/sequel/adapters/shared/postgres.rb
828 def _check_constraints_ds
829   @_check_constraints_ds ||= metadata_dataset.
830     from{pg_constraint.as(:co)}.
831     left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
832     where(:contype=>'c').
833     select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
834 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
837 def _foreign_key_list_ds
838   @_foreign_key_list_ds ||= __foreign_key_list_ds(false)
839 end
_indexes_ds() click to toggle source

Dataset used to retrieve index information

    # File lib/sequel/adapters/shared/postgres.rb
903 def _indexes_ds
904   @_indexes_ds ||= begin
905     if server_version >= 90500
906       order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
907     # :nocov:
908     else
909       range = 0...32
910       order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
911     # :nocov:
912     end
913 
914     attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey])
915 
916     ds = metadata_dataset.
917       from{pg_class.as(:tab)}.
918       join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
919       join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
920       join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
921       left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
922       where{{
923         indc[:relkind]=>%w'i I',
924         ind[:indisprimary]=>false,
925         :indexprs=>nil,
926         :indisvalid=>true}}.
927       order(*order).
928       select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}
929 
930     # :nocov:
931     ds = ds.where(:indisready=>true) if server_version >= 80300
932     ds = ds.where(:indislive=>true) if server_version >= 90300
933     # :nocov:
934 
935     ds
936   end
937 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
842 def _reverse_foreign_key_list_ds
843   @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true)
844 end
_schema_ds() click to toggle source

Dataset used to get schema for tables

     # File lib/sequel/adapters/shared/postgres.rb
1000 def _schema_ds
1001   @_schema_ds ||= begin
1002     ds = metadata_dataset.select{[
1003         pg_attribute[:attname].as(:name),
1004         SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid),
1005         SQL::Cast.new(basetype[:oid], :integer).as(:base_oid),
1006         SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type),
1007         SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type),
1008         SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default),
1009         SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null),
1010         SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key),
1011         Sequel[:pg_type][:typtype],
1012         (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array),
1013       ]}.
1014       from(:pg_class).
1015       join(:pg_attribute, :attrelid=>:oid).
1016       join(:pg_type, :oid=>:atttypid).
1017       left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype).
1018       left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]).
1019       left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]).
1020       left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true).
1021       where{{pg_attribute[:attisdropped]=>false}}.
1022       where{pg_attribute[:attnum] > 0}.
1023       order{pg_attribute[:attnum]}
1024 
1025     # :nocov:
1026     if server_version > 100000
1027     # :nocov:
1028       ds = ds.select_append{pg_attribute[:attidentity]}
1029 
1030       # :nocov:
1031       if server_version > 120000
1032       # :nocov:
1033         ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)}
1034       end
1035     end
1036 
1037     ds
1038   end
1039 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
940 def _select_custom_sequence_ds
941   @_select_custom_sequence_ds ||= metadata_dataset.
942     from{pg_class.as(:t)}.
943     join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name).
944     join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr).
945     join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def).
946     join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons).
947     where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}.
948     select{
949       expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2)
950       [
951         name[:nspname].as(:schema),
952         Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence)
953       ]
954     }
955 end
_select_pk_ds() click to toggle source

Dataset used to determine primary keys for tables

    # File lib/sequel/adapters/shared/postgres.rb
986 def _select_pk_ds
987   @_select_pk_ds ||= metadata_dataset.
988     from(:pg_class, :pg_attribute, :pg_index, :pg_namespace).
989     where{[
990       [pg_class[:oid], pg_attribute[:attrelid]],
991       [pg_class[:relnamespace], pg_namespace[:oid]],
992       [pg_class[:oid], pg_index[:indrelid]],
993       [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]],
994       [pg_index[:indisprimary], 't']
995     ]}.
996     select{pg_attribute[:attname].as(:pk)}
997 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
958 def _select_serial_sequence_ds
959   @_serial_sequence_ds ||= metadata_dataset.
960     from{[
961       pg_class.as(:seq),
962       pg_attribute.as(:attr),
963       pg_depend.as(:dep),
964       pg_namespace.as(:name),
965       pg_constraint.as(:cons),
966       pg_class.as(:t)
967     ]}.
968     where{[
969       [seq[:oid], dep[:objid]],
970       [seq[:relnamespace], name[:oid]],
971       [seq[:relkind], 'S'],
972       [attr[:attrelid], dep[:refobjid]],
973       [attr[:attnum], dep[:refobjsubid]],
974       [attr[:attrelid], cons[:conrelid]],
975       [attr[:attnum], cons[:conkey].sql_subscript(1)],
976       [attr[:attrelid], t[:oid]],
977       [cons[:contype], 'p']
978     ]}.
979     select{[
980       name[:nspname].as(:schema),
981       seq[:relname].as(:sequence)
982     ]}
983 end
alter_table_add_column_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1041 def alter_table_add_column_sql(table, op)
1042   "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}"
1043 end
alter_table_drop_column_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1059 def alter_table_drop_column_sql(table, op)
1060   "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}"
1061 end
alter_table_generator_class() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1045 def alter_table_generator_class
1046   Postgres::AlterTableGenerator
1047 end
alter_table_set_column_type_sql(table, op) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1049 def alter_table_set_column_type_sql(table, op)
1050   s = super
1051   if using = op[:using]
1052     using = Sequel::LiteralString.new(using) if using.is_a?(String)
1053     s += ' USING '
1054     s << literal(using)
1055   end
1056   s
1057 end
alter_table_validate_constraint_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1063 def alter_table_validate_constraint_sql(table, op)
1064   "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
1065 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
1070 def begin_new_transaction(conn, opts)
1071   super
1072   if opts.has_key?(:synchronous)
1073     case sync = opts[:synchronous]
1074     when true
1075       sync = :on
1076     when false
1077       sync = :off
1078     when nil
1079       return
1080     end
1081 
1082     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
1083   end
1084 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
1087 def begin_savepoint(conn, opts)
1088   super
1089 
1090   unless (read_only = opts[:read_only]).nil?
1091     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
1092   end
1093 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
1097 def column_definition_collate_sql(sql, column)
1098   if collate = column[:collate]
1099     collate = literal(collate) unless collate.is_a?(String)
1100     sql << " COLLATE #{collate}"
1101   end
1102 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
1106 def column_definition_default_sql(sql, column)
1107   super
1108   if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
1109     if (identity = column[:identity])
1110       sql << " GENERATED "
1111       sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
1112       sql << " AS IDENTITY"
1113     elsif (generated = column[:generated_always_as])
1114       sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED"
1115     end
1116   end
1117 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
1120 def column_schema_normalize_default(default, type)
1121   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
1122     default = m[1] || m[2]
1123   end
1124   super(default, type)
1125 end
combinable_alter_table_op?(op) click to toggle source

PostgreSQL can’t combine rename_column operations, and it can combine the custom validate_constraint operation.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1139 def combinable_alter_table_op?(op)
1140   (super || op[:op] == :validate_constraint) && op[:op] != :rename_column
1141 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
1129 def commit_transaction(conn, opts=OPTS)
1130   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
1131     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
1132   else
1133     super
1134   end
1135 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
1145 def connection_configuration_sqls(opts=@opts)
1146   sqls = []
1147 
1148   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
1149 
1150   cmm = opts.fetch(:client_min_messages, :warning)
1151   if cmm && !cmm.to_s.empty?
1152     cmm = cmm.to_s.upcase.strip
1153     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
1154       raise Error, "Unsupported client_min_messages setting: #{cmm}"
1155     end
1156     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
1157   end
1158 
1159   if search_path = opts[:search_path]
1160     case search_path
1161     when String
1162       search_path = search_path.split(",").map(&:strip)
1163     when Array
1164       # nil
1165     else
1166       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
1167     end
1168     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
1169   end
1170 
1171   sqls
1172 end
constraint_definition_sql(constraint) click to toggle source

Handle exclusion constraints.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1175 def constraint_definition_sql(constraint)
1176   case constraint[:type]
1177   when :exclude
1178     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
1179     sql = String.new
1180     sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}"
1181     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1182     sql
1183   when :foreign_key, :check
1184     sql = super
1185     if constraint[:not_valid]
1186       sql << " NOT VALID"
1187     end
1188     sql
1189   else
1190     super
1191   end
1192 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
1223 def copy_into_sql(table, opts)
1224   sql = String.new
1225   sql << "COPY #{literal(table)}"
1226   if cols = opts[:columns]
1227     sql << literal(Array(cols))
1228   end
1229   sql << " FROM STDIN"
1230   if opts[:options] || opts[:format]
1231     sql << " ("
1232     sql << "FORMAT #{opts[:format]}" if opts[:format]
1233     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1234     sql << ')'
1235   end
1236   sql
1237 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
1240 def copy_table_sql(table, opts)
1241   if table.is_a?(String)
1242     table
1243   else
1244     if opts[:options] || opts[:format]
1245       options = String.new
1246       options << " ("
1247       options << "FORMAT #{opts[:format]}" if opts[:format]
1248       options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1249       options << ')'
1250     end
1251     table = if table.is_a?(::Sequel::Dataset)
1252       "(#{table.sql})"
1253     else
1254       literal(table)
1255     end
1256     "COPY #{table} TO STDOUT#{options}"
1257   end
1258 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
1261       def create_function_sql(name, definition, opts=OPTS)
1262         args = opts[:args]
1263         if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)}
1264           returns = opts[:returns] || 'void'
1265         end
1266         language = opts[:language] || 'SQL'
1267         <<-END
1268         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
1269         #{"RETURNS #{returns}" if returns}
1270         LANGUAGE #{language}
1271         #{opts[:behavior].to_s.upcase if opts[:behavior]}
1272         #{'STRICT' if opts[:strict]}
1273         #{'SECURITY DEFINER' if opts[:security_definer]}
1274         #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]}
1275         #{"COST #{opts[:cost]}" if opts[:cost]}
1276         #{"ROWS #{opts[:rows]}" if opts[:rows]}
1277         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
1278         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
1279         END
1280       end
create_language_sql(name, opts=OPTS) click to toggle source

SQL for creating a procedural language.

     # File lib/sequel/adapters/shared/postgres.rb
1283 def create_language_sql(name, opts=OPTS)
1284   "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]}"
1285 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
1289 def create_partition_of_table_from_generator(name, generator, options)
1290   execute_ddl(create_partition_of_table_sql(name, generator, options))
1291 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
1294 def create_partition_of_table_sql(name, generator, options)
1295   sql = create_table_prefix_sql(name, options).dup
1296 
1297   sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}"
1298 
1299   case generator.partition_type
1300   when :range
1301     from, to = generator.range
1302     sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}"
1303   when :list
1304     sql << " FOR VALUES IN #{literal(generator.list)}"
1305   when :hash
1306     mod, remainder = generator.hash_values
1307     sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})"
1308   else # when :default
1309     sql << " DEFAULT"
1310   end
1311 
1312   sql << create_table_suffix_sql(name, options)
1313 
1314   sql
1315 end
create_schema_sql(name, opts=OPTS) click to toggle source

SQL for creating a schema.

     # File lib/sequel/adapters/shared/postgres.rb
1318 def create_schema_sql(name, opts=OPTS)
1319   "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}"
1320 end
create_table_as_sql(name, sql, options) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1376 def create_table_as_sql(name, sql, options)
1377   result = create_table_prefix_sql name, options
1378   if on_commit = options[:on_commit]
1379     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
1380   end
1381   result += " AS #{sql}"
1382 end
create_table_generator_class() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1384 def create_table_generator_class
1385   Postgres::CreateTableGenerator
1386 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
1323 def create_table_prefix_sql(name, options)
1324   prefix_sql = if options[:temp]
1325     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
1326     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
1327     temporary_table_sql
1328   elsif options[:foreign]
1329     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
1330     'FOREIGN '
1331   elsif options[:unlogged]
1332     'UNLOGGED '
1333   end
1334 
1335   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}"
1336 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
1339 def create_table_sql(name, generator, options)
1340   "#{super}#{create_table_suffix_sql(name, options)}"
1341 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
1345 def create_table_suffix_sql(name, options)
1346   sql = String.new
1347 
1348   if inherits = options[:inherits]
1349     sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
1350   end
1351 
1352   if partition_by = options[:partition_by]
1353     sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
1354   end
1355 
1356   if on_commit = options[:on_commit]
1357     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
1358     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
1359     sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
1360   end
1361 
1362   if tablespace = options[:tablespace]
1363     sql << " TABLESPACE #{quote_identifier(tablespace)}"
1364   end
1365 
1366   if server = options[:foreign]
1367     sql << " SERVER #{quote_identifier(server)}"
1368     if foreign_opts = options[:options]
1369       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
1370     end
1371   end
1372 
1373   sql
1374 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
1389 def create_trigger_sql(table, name, function, opts=OPTS)
1390   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
1391   whence = opts[:after] ? 'AFTER' : 'BEFORE'
1392   if filter = opts[:when]
1393     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
1394     filter = " WHEN #{filter_expr(filter)}"
1395   end
1396   "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(', ')})"
1397 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
1400 def create_view_prefix_sql(name, options)
1401   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])
1402 
1403   if options[:security_invoker]
1404     sql += " WITH (security_invoker)"
1405   end
1406 
1407   if tablespace = options[:tablespace]
1408     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1409   end
1410 
1411   sql
1412 end
database_error_regexps() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1218 def database_error_regexps
1219   DATABASE_ERROR_REGEXPS
1220 end
database_specific_error_class_from_sqlstate(sqlstate) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1194 def database_specific_error_class_from_sqlstate(sqlstate)
1195   if sqlstate == '23P01'
1196     ExclusionConstraintViolation
1197   elsif sqlstate == '40P01'
1198     SerializationFailure
1199   elsif sqlstate == '55P03'
1200     DatabaseLockTimeout
1201   else
1202     super
1203   end
1204 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
1415 def drop_function_sql(name, opts=OPTS)
1416   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
1417 end
drop_index_sql(table, op) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1420 def drop_index_sql(table, op)
1421   sch, _ = schema_and_table(table)
1422   "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]}"
1423 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
1426 def drop_language_sql(name, opts=OPTS)
1427   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1428 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
1431 def drop_schema_sql(name, opts=OPTS)
1432   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1433 end
drop_table_sql(name, options) click to toggle source

Support :foreign tables

     # File lib/sequel/adapters/shared/postgres.rb
1441 def drop_table_sql(name, options)
1442   "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
1443 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
1436 def drop_trigger_sql(table, name, opts=OPTS)
1437   "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
1438 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
1446 def drop_view_sql(name, opts=OPTS)
1447   "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
1448 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
1452 def filter_schema(ds, opts)
1453   expr = if schema = opts[:schema]
1454     schema.to_s
1455   else
1456     Sequel.function(:any, Sequel.function(:current_schemas, false))
1457   end
1458   ds.where{{pg_namespace[:nspname]=>expr}}
1459 end
index_definition_sql(table_name, index) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1461 def index_definition_sql(table_name, index)
1462   cols = index[:columns]
1463   index_name = index[:name] || default_index_name(table_name, cols)
1464 
1465   expr = if o = index[:opclass]
1466     "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
1467   else
1468     literal(Array(cols))
1469   end
1470 
1471   if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
1472   unique = "UNIQUE " if index[:unique]
1473   index_type = index[:type]
1474   filter = index[:where] || index[:filter]
1475   filter = " WHERE #{filter_expr(filter)}" if filter
1476   nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil?
1477 
1478   case index_type
1479   when :full_text
1480     expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
1481     index_type = index[:index_type] || :gin
1482   when :spatial
1483     index_type = :gist
1484   end
1485 
1486   "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}"
1487 end
initialize_postgres_adapter() click to toggle source

Setup datastructures shared by all postgres adapters.

     # File lib/sequel/adapters/shared/postgres.rb
1490 def initialize_postgres_adapter
1491   @primary_keys = {}
1492   @primary_key_sequences = {}
1493   @supported_types = {}
1494   procs = @conversion_procs = CONVERSION_PROCS.dup
1495   procs[1184] = procs[1114] = method(:to_application_timestamp)
1496 end
pg_class_relname(type, opts) { || ... } click to toggle source

Backbone of the tables and views support.

     # File lib/sequel/adapters/shared/postgres.rb
1499 def pg_class_relname(type, opts)
1500   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
1501   ds = filter_schema(ds, opts)
1502   m = output_identifier_meth
1503   if defined?(yield)
1504     yield(ds)
1505   elsif opts[:qualify]
1506     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
1507   else
1508     ds.map{|r| m.call(r[:relname])}
1509   end
1510 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
1514 def regclass_oid(expr, opts=OPTS)
1515   if expr.is_a?(String) && !expr.is_a?(LiteralString)
1516     expr = Sequel.identifier(expr)
1517   end
1518 
1519   sch, table = schema_and_table(expr)
1520   sch ||= opts[:schema]
1521   if sch
1522     expr = Sequel.qualify(sch, table)
1523   end
1524   
1525   expr = if ds = opts[:dataset]
1526     ds.literal(expr)
1527   else
1528     literal(expr)
1529   end
1530 
1531   Sequel.cast(expr.to_s,:regclass).cast(:oid)
1532 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
1535 def remove_cached_schema(table)
1536   tab = quote_schema_table(table)
1537   Sequel.synchronize do
1538     @primary_keys.delete(tab)
1539     @primary_key_sequences.delete(tab)
1540   end
1541   super
1542 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
1546 def rename_table_sql(name, new_name)
1547   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
1548 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
1563 def schema_array_type(db_type)
1564   :array
1565 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
1551 def schema_column_type(db_type)
1552   case db_type
1553   when /\Ainterval\z/io
1554     :interval
1555   when /\Acitext\z/io
1556     :string
1557   else
1558     super
1559   end
1560 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
1568 def schema_composite_type(db_type)
1569   :composite
1570 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
1573 def schema_enum_type(db_type)
1574   :enum
1575 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
1583 def schema_multirange_type(db_type)
1584   :multirange
1585 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
1600 def schema_parse_table(table_name, opts)
1601   m = output_identifier_meth(opts[:dataset])
1602 
1603   _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row|
1604     row[:default] = nil if blank_object?(row[:default])
1605     if row[:base_oid]
1606       row[:domain_oid] = row[:oid]
1607       row[:oid] = row.delete(:base_oid)
1608       row[:db_domain_type] = row[:db_type]
1609       row[:db_type] = row.delete(:db_base_type)
1610     else
1611       row.delete(:base_oid)
1612       row.delete(:db_base_type)
1613     end
1614 
1615     db_type = row[:db_type]
1616     row[:type] = if row.delete(:is_array)
1617       schema_array_type(db_type)
1618     else
1619       send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type)
1620     end
1621     identity = row.delete(:attidentity)
1622     if row[:primary_key]
1623       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
1624     end
1625 
1626     # :nocov:
1627     if server_version >= 90600
1628     # :nocov:
1629       case row[:oid]
1630       when 1082
1631         row[:min_value] = MIN_DATE
1632         row[:max_value] = MAX_DATE
1633       when 1184, 1114
1634         if Sequel.datetime_class == Time
1635           row[:min_value] = MIN_TIMESTAMP
1636           row[:max_value] = MAX_TIMESTAMP
1637         end
1638       end
1639     end
1640 
1641     [m.call(row.delete(:name)), row]
1642   end
1643 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
1578 def schema_range_type(db_type)
1579   :range
1580 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
1646 def set_transaction_isolation(conn, opts)
1647   level = opts.fetch(:isolation, transaction_isolation_level)
1648   read_only = opts[:read_only]
1649   deferrable = opts[:deferrable]
1650   if level || !read_only.nil? || !deferrable.nil?
1651     sql = String.new
1652     sql << "SET TRANSACTION"
1653     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
1654     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
1655     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
1656     log_connection_execute(conn, sql)
1657   end
1658 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
1661 def sql_function_args(args)
1662   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
1663 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
1666 def supports_combining_alter_table_ops?
1667   true
1668 end
supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1671 def supports_create_or_replace_view?
1672   true
1673 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
1676 def type_literal_generic_bignum_symbol(column)
1677   column[:serial] ? :bigserial : super
1678 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
1681 def type_literal_generic_file(column)
1682   :bytea
1683 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
1686 def type_literal_generic_integer(column)
1687   column[:serial] ? :serial : super
1688 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
1694 def type_literal_generic_string(column)
1695   if column[:text]
1696     :text
1697   elsif column[:fixed]
1698     "char(#{column[:size]||default_string_column_size})"
1699   elsif column[:text] == false || column[:size]
1700     "varchar(#{column[:size]||default_string_column_size})"
1701   else
1702     :text
1703   end
1704 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
1707 def view_with_check_option_support
1708   # :nocov:
1709   :local if server_version >= 90400
1710   # :nocov:
1711 end