module Sequel::Postgres::DatabaseMethods

Constants

DATABASE_ERROR_REGEXPS
FOREIGN_KEY_LIST_ON_DELETE_MAP
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.

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   @default_dataset.clone(:values=>v)
809 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
819 def views(opts=OPTS)
820   relkind = opts[:materialized] ? 'm' : 'v'
821   pg_class_relname(relkind, opts)
822 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
846 def __foreign_key_list_ds(reverse)
847   if reverse
848     ctable = Sequel[:att2]
849     cclass = Sequel[:cl2]
850     rtable = Sequel[:att]
851     rclass = Sequel[:cl]
852   else
853     ctable = Sequel[:att]
854     cclass = Sequel[:cl]
855     rtable = Sequel[:att2]
856     rclass = Sequel[:cl2]
857   end
858 
859   if server_version >= 90500
860     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
861     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
862   # :nocov:
863   else
864     range = 0...32
865     cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
866     rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
867   # :nocov:
868   end
869 
870   ds = metadata_dataset.
871     from{pg_constraint.as(:co)}.
872     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
873     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
874     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
875     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
876     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
877     order{[co[:conname], cpos]}.
878     where{{
879       cl[:relkind]=>%w'r p',
880       co[:contype]=>'f',
881       cpos=>rpos
882     }}.
883     select{[
884       co[:conname].as(:name),
885       ctable[:attname].as(:column),
886       co[:confupdtype].as(:on_update),
887       co[:confdeltype].as(:on_delete),
888       cl2[:relname].as(:table),
889       rtable[:attname].as(:refcolumn),
890       SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
891       nsp[:nspname].as(:schema)
892     ]}
893 
894   if reverse
895     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
896   end
897 
898   ds
899 end
_check_constraints_ds() click to toggle source

Dataset used to retrieve CHECK constraint information

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

Dataset used to retrieve index information

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

Dataset used to get schema for tables

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

Dataset used to determine primary keys for tables

    # File lib/sequel/adapters/shared/postgres.rb
985 def _select_pk_ds
986   @_select_pk_ds ||= metadata_dataset.
987     from(:pg_class, :pg_attribute, :pg_index, :pg_namespace).
988     where{[
989       [pg_class[:oid], pg_attribute[:attrelid]],
990       [pg_class[:relnamespace], pg_namespace[:oid]],
991       [pg_class[:oid], pg_index[:indrelid]],
992       [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]],
993       [pg_index[:indisprimary], 't']
994     ]}.
995     select{pg_attribute[:attname].as(:pk)}
996 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
957 def _select_serial_sequence_ds
958   @_serial_sequence_ds ||= metadata_dataset.
959     from{[
960       pg_class.as(:seq),
961       pg_attribute.as(:attr),
962       pg_depend.as(:dep),
963       pg_namespace.as(:name),
964       pg_constraint.as(:cons),
965       pg_class.as(:t)
966     ]}.
967     where{[
968       [seq[:oid], dep[:objid]],
969       [seq[:relnamespace], name[:oid]],
970       [seq[:relkind], 'S'],
971       [attr[:attrelid], dep[:refobjid]],
972       [attr[:attnum], dep[:refobjsubid]],
973       [attr[:attrelid], cons[:conrelid]],
974       [attr[:attnum], cons[:conkey].sql_subscript(1)],
975       [attr[:attrelid], t[:oid]],
976       [cons[:contype], 'p']
977     ]}.
978     select{[
979       name[:nspname].as(:schema),
980       seq[:relname].as(:sequence)
981     ]}
982 end
alter_table_add_column_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1036 def alter_table_add_column_sql(table, op)
1037   "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}"
1038 end
alter_table_drop_column_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1054 def alter_table_drop_column_sql(table, op)
1055   "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}"
1056 end
alter_table_generator_class() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1040 def alter_table_generator_class
1041   Postgres::AlterTableGenerator
1042 end
alter_table_set_column_type_sql(table, op) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1044 def alter_table_set_column_type_sql(table, op)
1045   s = super
1046   if using = op[:using]
1047     using = Sequel::LiteralString.new(using) if using.is_a?(String)
1048     s += ' USING '
1049     s << literal(using)
1050   end
1051   s
1052 end
alter_table_validate_constraint_sql(table, op) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1058 def alter_table_validate_constraint_sql(table, op)
1059   "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
1060 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
1065 def begin_new_transaction(conn, opts)
1066   super
1067   if opts.has_key?(:synchronous)
1068     case sync = opts[:synchronous]
1069     when true
1070       sync = :on
1071     when false
1072       sync = :off
1073     when nil
1074       return
1075     end
1076 
1077     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
1078   end
1079 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
1082 def begin_savepoint(conn, opts)
1083   super
1084 
1085   unless (read_only = opts[:read_only]).nil?
1086     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
1087   end
1088 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
1092 def column_definition_collate_sql(sql, column)
1093   if collate = column[:collate]
1094     collate = literal(collate) unless collate.is_a?(String)
1095     sql << " COLLATE #{collate}"
1096   end
1097 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
1101 def column_definition_default_sql(sql, column)
1102   super
1103   if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
1104     if (identity = column[:identity])
1105       sql << " GENERATED "
1106       sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
1107       sql << " AS IDENTITY"
1108     elsif (generated = column[:generated_always_as])
1109       sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED"
1110     end
1111   end
1112 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
1115 def column_schema_normalize_default(default, type)
1116   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
1117     default = m[1] || m[2]
1118   end
1119   super(default, type)
1120 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
1134 def combinable_alter_table_op?(op)
1135   (super || op[:op] == :validate_constraint) && op[:op] != :rename_column
1136 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
1124 def commit_transaction(conn, opts=OPTS)
1125   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
1126     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
1127   else
1128     super
1129   end
1130 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
1140 def connection_configuration_sqls(opts=@opts)
1141   sqls = []
1142 
1143   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
1144 
1145   cmm = opts.fetch(:client_min_messages, :warning)
1146   if cmm && !cmm.to_s.empty?
1147     cmm = cmm.to_s.upcase.strip
1148     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
1149       raise Error, "Unsupported client_min_messages setting: #{cmm}"
1150     end
1151     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
1152   end
1153 
1154   if search_path = opts[:search_path]
1155     case search_path
1156     when String
1157       search_path = search_path.split(",").map(&:strip)
1158     when Array
1159       # nil
1160     else
1161       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
1162     end
1163     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
1164   end
1165 
1166   sqls
1167 end
constraint_definition_sql(constraint) click to toggle source

Handle exclusion constraints.

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

SQL for creating a procedural language.

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

SQL for creating a schema.

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

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

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

Support :foreign tables

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

Setup datastructures shared by all postgres adapters.

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

Backbone of the tables and views support.

     # File lib/sequel/adapters/shared/postgres.rb
1494 def pg_class_relname(type, opts)
1495   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
1496   ds = filter_schema(ds, opts)
1497   m = output_identifier_meth
1498   if defined?(yield)
1499     yield(ds)
1500   elsif opts[:qualify]
1501     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
1502   else
1503     ds.map{|r| m.call(r[:relname])}
1504   end
1505 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
1509 def regclass_oid(expr, opts=OPTS)
1510   if expr.is_a?(String) && !expr.is_a?(LiteralString)
1511     expr = Sequel.identifier(expr)
1512   end
1513 
1514   sch, table = schema_and_table(expr)
1515   sch ||= opts[:schema]
1516   if sch
1517     expr = Sequel.qualify(sch, table)
1518   end
1519   
1520   expr = if ds = opts[:dataset]
1521     ds.literal(expr)
1522   else
1523     literal(expr)
1524   end
1525 
1526   Sequel.cast(expr.to_s,:regclass).cast(:oid)
1527 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
1530 def remove_cached_schema(table)
1531   tab = quote_schema_table(table)
1532   Sequel.synchronize do
1533     @primary_keys.delete(tab)
1534     @primary_key_sequences.delete(tab)
1535   end
1536   super
1537 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 speciying a new schema in new_name will not have an effect.

     # File lib/sequel/adapters/shared/postgres.rb
1541 def rename_table_sql(name, new_name)
1542   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
1543 end
schema_column_type(db_type) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1545 def schema_column_type(db_type)
1546   case db_type
1547   when /\Ainterval\z/io
1548     :interval
1549   when /\Acitext\z/io
1550     :string
1551   else
1552     super
1553   end
1554 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
1557 def schema_parse_table(table_name, opts)
1558   m = output_identifier_meth(opts[:dataset])
1559 
1560   _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row|
1561     row[:default] = nil if blank_object?(row[:default])
1562     if row[:base_oid]
1563       row[:domain_oid] = row[:oid]
1564       row[:oid] = row.delete(:base_oid)
1565       row[:db_domain_type] = row[:db_type]
1566       row[:db_type] = row.delete(:db_base_type)
1567     else
1568       row.delete(:base_oid)
1569       row.delete(:db_base_type)
1570     end
1571     row[:type] = schema_column_type(row[:db_type])
1572     identity = row.delete(:attidentity)
1573     if row[:primary_key]
1574       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
1575     end
1576     [m.call(row.delete(:name)), row]
1577   end
1578 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
1581 def set_transaction_isolation(conn, opts)
1582   level = opts.fetch(:isolation, transaction_isolation_level)
1583   read_only = opts[:read_only]
1584   deferrable = opts[:deferrable]
1585   if level || !read_only.nil? || !deferrable.nil?
1586     sql = String.new
1587     sql << "SET TRANSACTION"
1588     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
1589     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
1590     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
1591     log_connection_execute(conn, sql)
1592   end
1593 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
1596 def sql_function_args(args)
1597   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
1598 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
1601 def supports_combining_alter_table_ops?
1602   true
1603 end
supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1606 def supports_create_or_replace_view?
1607   true
1608 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
1611 def type_literal_generic_bignum_symbol(column)
1612   column[:serial] ? :bigserial : super
1613 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
1616 def type_literal_generic_file(column)
1617   :bytea
1618 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
1621 def type_literal_generic_integer(column)
1622   column[:serial] ? :serial : super
1623 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
1629 def type_literal_generic_string(column)
1630   if column[:text]
1631     :text
1632   elsif column[:fixed]
1633     "char(#{column[:size]||default_string_column_size})"
1634   elsif column[:text] == false || column[:size]
1635     "varchar(#{column[:size]||default_string_column_size})"
1636   else
1637     :text
1638   end
1639 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
1642 def view_with_check_option_support
1643   # :nocov:
1644   :local if server_version >= 90400
1645   # :nocov:
1646 end