module Sequel::Postgres::DatabaseMethods

Constants

DATABASE_ERROR_REGEXPS
FOREIGN_KEY_LIST_ON_DELETE_MAP
ON_COMMIT
PREPARED_ARG_PLACEHOLDER
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
296 def add_conversion_proc(oid, callable=nil, &block)
297   conversion_procs[oid] = callable || block
298 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
303 def add_named_conversion_proc(name, &block)
304   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
305     raise Error, "No matching type in pg_type for #{name.inspect}"
306   end
307   add_conversion_proc(oid, block)
308 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
319 def check_constraints(table)
320   m = output_identifier_meth
321 
322   rows = metadata_dataset.
323     from{pg_constraint.as(:co)}.
324     left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
325     where(:conrelid=>regclass_oid(table), :contype=>'c').
326     select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
327 
328   hash = {}
329   rows.each do |row|
330     constraint = m.call(row[:constraint])
331     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]}
332     entry[:columns] << m.call(row[:column]) if row[:column]
333   end
334   
335   hash
336 end
commit_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
310 def commit_prepared_transaction(transaction_id, opts=OPTS)
311   run("COMMIT PREPARED #{literal(transaction_id)}", opts)
312 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
356 def convert_serial_to_identity(table, opts=OPTS)
357   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
358 
359   server = opts[:server]
360   server_hash = server ? {:server=>server} : OPTS
361   ds = dataset
362   ds = ds.server(server) if server
363 
364   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
365 
366   table_oid = regclass_oid(table)
367   im = input_identifier_meth
368   unless column = im.call(opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
369     raise Error, "could not determine column to convert from serial to identity automatically"
370   end
371 
372   column_num = ds.from(:pg_attribute).
373     where(:attrelid=>table_oid, :attname=>column).
374     get(:attnum)
375 
376   pg_class = Sequel.cast('pg_class', :regclass)
377   res = ds.from(:pg_depend).
378     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
379     select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])
380 
381   case res.length
382   when 0
383     raise Error, "unable to find related sequence when converting serial to identity"
384   when 1
385     seq_oid, already_identity = res.first
386   else
387     raise Error, "more than one linked sequence found when converting serial to identity"
388   end
389 
390   return if already_identity
391 
392   transaction(server_hash) do
393     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash)
394 
395     ds.from(:pg_depend).
396       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
397       update(:deptype=>'i')
398 
399     ds.from(:pg_attribute).
400       where(:attrelid=>table_oid, :attname=>column).
401       update(:attidentity=>'d')
402   end
403 
404   remove_cached_schema(table)
405   nil
406 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.

: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
428 def create_function(name, definition, opts=OPTS)
429   self << create_function_sql(name, definition, opts)
430 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
439 def create_language(name, opts=OPTS)
440   self << create_language_sql(name, opts)
441 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
448 def create_schema(name, opts=OPTS)
449   self << create_schema_sql(name, opts)
450 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
453 def create_table(name, options=OPTS, &block)
454   if options[:partition_of]
455     create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
456     return
457   end
458 
459   super
460 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
463 def create_table?(name, options=OPTS, &block)
464   if options[:partition_of]
465     create_table(name, options.merge!(:if_not_exists=>true), &block)
466     return
467   end
468 
469   super
470 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.

:when

A filter to use for the trigger

    # File lib/sequel/adapters/shared/postgres.rb
483 def create_trigger(table, name, function, opts=OPTS)
484   self << create_trigger_sql(table, name, function, opts)
485 end
database_type() click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
487 def database_type
488   :postgres
489 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
496 def do(code, opts=OPTS)
497   language = opts[:language]
498   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}"
499 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
507 def drop_function(name, opts=OPTS)
508   self << drop_function_sql(name, opts)
509 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
516 def drop_language(name, opts=OPTS)
517   self << drop_language_sql(name, opts)
518 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
525 def drop_schema(name, opts=OPTS)
526   self << drop_schema_sql(name, opts)
527 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
535 def drop_trigger(table, name, opts=OPTS)
536   self << drop_trigger_sql(table, name, opts)
537 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
549 def foreign_key_list(table, opts=OPTS)
550   m = output_identifier_meth
551   schema, _ = opts.fetch(:schema, schema_and_table(table))
552   oid = regclass_oid(table)
553   reverse = opts[:reverse]
554 
555   if reverse
556     ctable = Sequel[:att2]
557     cclass = Sequel[:cl2]
558     rtable = Sequel[:att]
559     rclass = Sequel[:cl]
560   else
561     ctable = Sequel[:att]
562     cclass = Sequel[:cl]
563     rtable = Sequel[:att2]
564     rclass = Sequel[:cl2]
565   end
566 
567   if server_version >= 90500
568     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
569     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
570   else
571     range = 0...32
572     cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
573     rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
574   end
575 
576   ds = metadata_dataset.
577     from{pg_constraint.as(:co)}.
578     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
579     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
580     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
581     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
582     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
583     order{[co[:conname], cpos]}.
584     where{{
585       cl[:relkind]=>'r',
586       co[:contype]=>'f',
587       cl[:oid]=>oid,
588       cpos=>rpos
589     }}.
590     select{[
591       co[:conname].as(:name),
592       ctable[:attname].as(:column),
593       co[:confupdtype].as(:on_update),
594       co[:confdeltype].as(:on_delete),
595       cl2[:relname].as(:table),
596       rtable[:attname].as(:refcolumn),
597       SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
598       nsp[:nspname].as(:schema)
599     ]}
600 
601   if reverse
602     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
603   end
604 
605   h = {}
606   fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
607 
608   ds.each do |row|
609     if reverse
610       key = [row[:schema], row[:table], row[:name]]
611     else
612       key = row[:name]
613     end
614 
615     if r = h[key]
616       r[:columns] << m.call(row[:column])
617       r[:key] << m.call(row[:refcolumn])
618     else
619       entry = h[key] = {
620         :name=>m.call(row[:name]),
621         :columns=>[m.call(row[:column])],
622         :key=>[m.call(row[:refcolumn])],
623         :on_update=>fklod_map[row[:on_update]],
624         :on_delete=>fklod_map[row[:on_delete]],
625         :deferrable=>row[:deferrable],
626         :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
627       }
628 
629       unless schema
630         # If not combining schema information into the :table entry
631         # include it as a separate entry.
632         entry[:schema] = m.call(row[:schema])
633       end
634     end
635   end
636 
637   h.values
638 end
freeze() click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/postgres.rb
640 def freeze
641   server_version
642   supports_prepared_transactions?
643   @conversion_procs.freeze
644   super
645 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
648 def indexes(table, opts=OPTS)
649   m = output_identifier_meth
650   oid = regclass_oid(table, opts)
651 
652   if server_version >= 90500
653     order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
654   else
655     range = 0...32
656     order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
657   end
658 
659   attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey])
660 
661   ds = metadata_dataset.
662     from{pg_class.as(:tab)}.
663     join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
664     join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
665     join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
666     left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
667     where{{
668       indc[:relkind]=>'i',
669       ind[:indisprimary]=>false,
670       :indexprs=>nil,
671       :indisvalid=>true,
672       tab[:oid]=>oid}}.
673     order(*order).
674     select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}
675 
676   ds = ds.where(:indpred=>nil) unless opts[:include_partial]
677   ds = ds.where(:indisready=>true) if server_version >= 80300
678   ds = ds.where(:indislive=>true) if server_version >= 90300
679 
680   indexes = {}
681   ds.each do |r|
682     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
683     i[:columns] << m.call(r[:column])
684   end
685   indexes
686 end
locks() click to toggle source

Dataset containing all current database locks

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

Return primary key for the given table.

    # File lib/sequel/adapters/shared/postgres.rb
711 def primary_key(table, opts=OPTS)
712   quoted_table = quote_schema_table(table)
713   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
714   sql = "#{SELECT_PK_SQL} AND pg_class.oid = #{literal(regclass_oid(table, opts))}"
715   value = fetch(sql).single_value
716   Sequel.synchronize{@primary_keys[quoted_table] = value}
717 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
720 def primary_key_sequence(table, opts=OPTS)
721   quoted_table = quote_schema_table(table)
722   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
723   sql = "#{SELECT_SERIAL_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
724   if pks = fetch(sql).single_record
725     value = literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
726     Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
727   else
728     sql = "#{SELECT_CUSTOM_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
729     if pks = fetch(sql).single_record
730       value = literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
731       Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
732     end
733   end
734 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
742 def refresh_view(name, opts=OPTS)
743   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
744 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
748 def reset_primary_key_sequence(table)
749   return unless seq = primary_key_sequence(table)
750   pk = SQL::Identifier.new(primary_key(table))
751   db = self
752   s, t = schema_and_table(table)
753   table = Sequel.qualify(s, t) if s
754 
755   if server_version >= 100000
756     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq)))
757     increment_by = :seqincrement
758     min_value = :seqmin
759   else
760     seq_ds = metadata_dataset.from(LiteralString.new(seq))
761     increment_by = :increment_by
762     min_value = :min_value
763   end
764 
765   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
766 end
rollback_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
768 def rollback_prepared_transaction(transaction_id, opts=OPTS)
769   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
770 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
774 def serial_primary_key_options
775   auto_increment_key = server_version >= 100002 ? :identity : :serial
776   {:primary_key => true, auto_increment_key => true, :type=>Integer}
777 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
780 def server_version(server=nil)
781   return @server_version if @server_version
782   ds = dataset
783   ds = ds.server(server) if server
784   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
785 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
788 def supports_create_table_if_not_exists?
789   server_version >= 90100
790 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
793 def supports_deferrable_constraints?
794   server_version >= 90000
795 end
supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

    # File lib/sequel/adapters/shared/postgres.rb
798 def supports_deferrable_foreign_key_constraints?
799   true
800 end
supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

    # File lib/sequel/adapters/shared/postgres.rb
803 def supports_drop_table_if_exists?
804   true
805 end
supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

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

PostgreSQL supports savepoints

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

PostgreSQL supports transaction isolation levels

    # File lib/sequel/adapters/shared/postgres.rb
830 def supports_transaction_isolation_levels?
831   true
832 end
supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

    # File lib/sequel/adapters/shared/postgres.rb
835 def supports_transactional_ddl?
836   true
837 end
supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
813 def supports_trigger_conditions?
814   server_version >= 90000
815 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
848 def tables(opts=OPTS, &block)
849   pg_class_relname(['r', 'p'], opts, &block)
850 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
854 def type_supported?(type)
855   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
856   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
857   Sequel.synchronize{return @supported_types[type] = supported}
858 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
867 def values(v)
868   @default_dataset.clone(:values=>v)
869 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
879 def views(opts=OPTS)
880   relkind = opts[:materialized] ? 'm' : 'v'
881   pg_class_relname(relkind, opts)
882 end

Private Instance Methods

alter_table_add_column_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
886 def alter_table_add_column_sql(table, op)
887   "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}"
888 end
alter_table_drop_column_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
904 def alter_table_drop_column_sql(table, op)
905   "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}"
906 end
alter_table_generator_class() click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
890 def alter_table_generator_class
891   Postgres::AlterTableGenerator
892 end
alter_table_set_column_type_sql(table, op) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/postgres.rb
894 def alter_table_set_column_type_sql(table, op)
895   s = super
896   if using = op[:using]
897     using = Sequel::LiteralString.new(using) if using.is_a?(String)
898     s += ' USING '
899     s << literal(using)
900   end
901   s
902 end
alter_table_validate_constraint_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
908 def alter_table_validate_constraint_sql(table, op)
909   "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
910 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
915 def begin_new_transaction(conn, opts)
916   super
917   if opts.has_key?(:synchronous)
918     case sync = opts[:synchronous]
919     when true
920       sync = :on
921     when false
922       sync = :off
923     when nil
924       return
925     end
926 
927     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
928   end
929 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
932 def begin_savepoint(conn, opts)
933   super
934 
935   unless (read_only = opts[:read_only]).nil?
936     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
937   end
938 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
942 def column_definition_collate_sql(sql, column)
943   if collate = column[:collate]
944     collate = literal(collate) unless collate.is_a?(String)
945     sql << " COLLATE #{collate}"
946   end
947 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
951 def column_definition_default_sql(sql, column)
952   super
953   if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
954     if (identity = column[:identity])
955       sql << " GENERATED "
956       sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
957       sql << " AS IDENTITY"
958     elsif (generated = column[:generated_always_as])
959       sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED"
960     end
961   end
962 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
965 def column_schema_normalize_default(default, type)
966   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
967     default = m[1] || m[2]
968   end
969   super(default, type)
970 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
984 def combinable_alter_table_op?(op)
985   (super || op[:op] == :validate_constraint) && op[:op] != :rename_column
986 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
974 def commit_transaction(conn, opts=OPTS)
975   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
976     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
977   else
978     super
979   end
980 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
 990 def connection_configuration_sqls(opts=@opts)
 991   sqls = []
 992 
 993   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
 994 
 995   cmm = opts.fetch(:client_min_messages, :warning)
 996   if cmm && !cmm.to_s.empty?
 997     cmm = cmm.to_s.upcase.strip
 998     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
 999       raise Error, "Unsupported client_min_messages setting: #{cmm}"
1000     end
1001     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
1002   end
1003 
1004   if search_path = opts[:search_path]
1005     case search_path
1006     when String
1007       search_path = search_path.split(",").map(&:strip)
1008     when Array
1009       # nil
1010     else
1011       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
1012     end
1013     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
1014   end
1015 
1016   sqls
1017 end
constraint_definition_sql(constraint) click to toggle source

Handle exclusion constraints.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1020 def constraint_definition_sql(constraint)
1021   case constraint[:type]
1022   when :exclude
1023     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
1024     sql = String.new
1025     sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}"
1026     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1027     sql
1028   when :foreign_key, :check
1029     sql = super
1030     if constraint[:not_valid]
1031       sql << " NOT VALID"
1032     end
1033     sql
1034   else
1035     super
1036   end
1037 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
1068 def copy_into_sql(table, opts)
1069   sql = String.new
1070   sql << "COPY #{literal(table)}"
1071   if cols = opts[:columns]
1072     sql << literal(Array(cols))
1073   end
1074   sql << " FROM STDIN"
1075   if opts[:options] || opts[:format]
1076     sql << " ("
1077     sql << "FORMAT #{opts[:format]}" if opts[:format]
1078     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1079     sql << ')'
1080   end
1081   sql
1082 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
1085 def copy_table_sql(table, opts)
1086   if table.is_a?(String)
1087     table
1088   else
1089     if opts[:options] || opts[:format]
1090       options = String.new
1091       options << " ("
1092       options << "FORMAT #{opts[:format]}" if opts[:format]
1093       options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1094       options << ')'
1095     end
1096     table = if table.is_a?(::Sequel::Dataset)
1097       "(#{table.sql})"
1098     else
1099       literal(table)
1100     end
1101     "COPY #{table} TO STDOUT#{options}"
1102   end
1103 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
1106       def create_function_sql(name, definition, opts=OPTS)
1107         args = opts[:args]
1108         if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)}
1109           returns = opts[:returns] || 'void'
1110         end
1111         language = opts[:language] || 'SQL'
1112         <<-END
1113         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
1114         #{"RETURNS #{returns}" if returns}
1115         LANGUAGE #{language}
1116         #{opts[:behavior].to_s.upcase if opts[:behavior]}
1117         #{'STRICT' if opts[:strict]}
1118         #{'SECURITY DEFINER' if opts[:security_definer]}
1119         #{"COST #{opts[:cost]}" if opts[:cost]}
1120         #{"ROWS #{opts[:rows]}" if opts[:rows]}
1121         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
1122         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
1123         END
1124       end
create_language_sql(name, opts=OPTS) click to toggle source

SQL for creating a procedural language.

     # File lib/sequel/adapters/shared/postgres.rb
1127 def create_language_sql(name, opts=OPTS)
1128   "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]}"
1129 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
1133 def create_partition_of_table_from_generator(name, generator, options)
1134   execute_ddl(create_partition_of_table_sql(name, generator, options))
1135 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
1138 def create_partition_of_table_sql(name, generator, options)
1139   sql = create_table_prefix_sql(name, options).dup
1140 
1141   sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}"
1142 
1143   case generator.partition_type
1144   when :range
1145     from, to = generator.range
1146     sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}"
1147   when :list
1148     sql << " FOR VALUES IN #{literal(generator.list)}"
1149   when :hash
1150     mod, remainder = generator.hash_values
1151     sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})"
1152   when :default
1153     sql << " DEFAULT"
1154   end
1155 
1156   sql << create_table_suffix_sql(name, options)
1157 
1158   sql
1159 end
create_schema_sql(name, opts=OPTS) click to toggle source

SQL for creating a schema.

     # File lib/sequel/adapters/shared/postgres.rb
1162 def create_schema_sql(name, opts=OPTS)
1163   "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}"
1164 end
create_table_as_sql(name, sql, options) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1220 def create_table_as_sql(name, sql, options)
1221   result = create_table_prefix_sql name, options
1222   if on_commit = options[:on_commit]
1223     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
1224   end
1225   result += " AS #{sql}"
1226 end
create_table_generator_class() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1228 def create_table_generator_class
1229   Postgres::CreateTableGenerator
1230 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
1167 def create_table_prefix_sql(name, options)
1168   prefix_sql = if options[:temp]
1169     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
1170     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
1171     temporary_table_sql
1172   elsif options[:foreign]
1173     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
1174     'FOREIGN '
1175   elsif options[:unlogged]
1176     'UNLOGGED '
1177   end
1178 
1179   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}"
1180 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
1183 def create_table_sql(name, generator, options)
1184   "#{super}#{create_table_suffix_sql(name, options)}"
1185 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
1189 def create_table_suffix_sql(name, options)
1190   sql = String.new
1191 
1192   if inherits = options[:inherits]
1193     sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
1194   end
1195 
1196   if partition_by = options[:partition_by]
1197     sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
1198   end
1199 
1200   if on_commit = options[:on_commit]
1201     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
1202     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
1203     sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
1204   end
1205 
1206   if tablespace = options[:tablespace]
1207     sql << " TABLESPACE #{quote_identifier(tablespace)}"
1208   end
1209 
1210   if server = options[:foreign]
1211     sql << " SERVER #{quote_identifier(server)}"
1212     if foreign_opts = options[:options]
1213       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
1214     end
1215   end
1216 
1217   sql
1218 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
1233 def create_trigger_sql(table, name, function, opts=OPTS)
1234   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
1235   whence = opts[:after] ? 'AFTER' : 'BEFORE'
1236   if filter = opts[:when]
1237     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
1238     filter = " WHEN #{filter_expr(filter)}"
1239   end
1240   "CREATE 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(', ')})"
1241 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
1244 def create_view_prefix_sql(name, options)
1245   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])
1246 
1247   if tablespace = options[:tablespace]
1248     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1249   end
1250 
1251   sql
1252 end
database_error_regexps() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1063 def database_error_regexps
1064   DATABASE_ERROR_REGEXPS
1065 end
database_specific_error_class_from_sqlstate(sqlstate) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1039 def database_specific_error_class_from_sqlstate(sqlstate)
1040   if sqlstate == '23P01'
1041     ExclusionConstraintViolation
1042   elsif sqlstate == '40P01'
1043     SerializationFailure
1044   elsif sqlstate == '55P03'
1045     DatabaseLockTimeout
1046   else
1047     super
1048   end
1049 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
1255 def drop_function_sql(name, opts=OPTS)
1256   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
1257 end
drop_index_sql(table, op) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1260 def drop_index_sql(table, op)
1261   sch, _ = schema_and_table(table)
1262   "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]}"
1263 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
1266 def drop_language_sql(name, opts=OPTS)
1267   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1268 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
1271 def drop_schema_sql(name, opts=OPTS)
1272   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1273 end
drop_table_sql(name, options) click to toggle source

Support :foreign tables

     # File lib/sequel/adapters/shared/postgres.rb
1281 def drop_table_sql(name, options)
1282   "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
1283 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
1276 def drop_trigger_sql(table, name, opts=OPTS)
1277   "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
1278 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
1286 def drop_view_sql(name, opts=OPTS)
1287   "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
1288 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
1292 def filter_schema(ds, opts)
1293   expr = if schema = opts[:schema]
1294     schema.to_s
1295   else
1296     Sequel.function(:any, Sequel.function(:current_schemas, false))
1297   end
1298   ds.where{{pg_namespace[:nspname]=>expr}}
1299 end
index_definition_sql(table_name, index) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1301 def index_definition_sql(table_name, index)
1302   cols = index[:columns]
1303   index_name = index[:name] || default_index_name(table_name, cols)
1304   expr = if o = index[:opclass]
1305     "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
1306   else
1307     literal(Array(cols))
1308   end
1309   if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
1310   unique = "UNIQUE " if index[:unique]
1311   index_type = index[:type]
1312   filter = index[:where] || index[:filter]
1313   filter = " WHERE #{filter_expr(filter)}" if filter
1314   case index_type
1315   when :full_text
1316     expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
1317     index_type = index[:index_type] || :gin
1318   when :spatial
1319     index_type = :gist
1320   end
1321   "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]}#{" TABLESPACE #{quote_identifier(index[:tablespace])}" if index[:tablespace]}#{filter}"
1322 end
initialize_postgres_adapter() click to toggle source

Setup datastructures shared by all postgres adapters.

     # File lib/sequel/adapters/shared/postgres.rb
1325 def initialize_postgres_adapter
1326   @primary_keys = {}
1327   @primary_key_sequences = {}
1328   @supported_types = {}
1329   procs = @conversion_procs = CONVERSION_PROCS.dup
1330   procs[1184] = procs[1114] = method(:to_application_timestamp)
1331 end
pg_class_relname(type, opts) { |ds| ... } click to toggle source

Backbone of the tables and views support.

     # File lib/sequel/adapters/shared/postgres.rb
1334 def pg_class_relname(type, opts)
1335   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
1336   ds = filter_schema(ds, opts)
1337   m = output_identifier_meth
1338   if block_given?
1339     yield(ds)
1340   elsif opts[:qualify]
1341     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
1342   else
1343     ds.map{|r| m.call(r[:relname])}
1344   end
1345 end
prepared_arg_placeholder() click to toggle source

Use a dollar sign instead of question mark for the argument placeholder.

     # File lib/sequel/adapters/shared/postgres.rb
1348 def prepared_arg_placeholder
1349   PREPARED_ARG_PLACEHOLDER
1350 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
1354 def regclass_oid(expr, opts=OPTS)
1355   if expr.is_a?(String) && !expr.is_a?(LiteralString)
1356     expr = Sequel.identifier(expr)
1357   end
1358 
1359   sch, table = schema_and_table(expr)
1360   sch ||= opts[:schema]
1361   if sch
1362     expr = Sequel.qualify(sch, table)
1363   end
1364   
1365   expr = if ds = opts[:dataset]
1366     ds.literal(expr)
1367   else
1368     literal(expr)
1369   end
1370 
1371   Sequel.cast(expr.to_s,:regclass).cast(:oid)
1372 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
1375 def remove_cached_schema(table)
1376   tab = quote_schema_table(table)
1377   Sequel.synchronize do
1378     @primary_keys.delete(tab)
1379     @primary_key_sequences.delete(tab)
1380   end
1381   super
1382 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
1386 def rename_table_sql(name, new_name)
1387   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
1388 end
schema_column_type(db_type) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1390 def schema_column_type(db_type)
1391   case db_type
1392   when /\Ainterval\z/io
1393     :interval
1394   when /\Acitext\z/io
1395     :string
1396   else
1397     super
1398   end
1399 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
1402 def schema_parse_table(table_name, opts)
1403   m = output_identifier_meth(opts[:dataset])
1404   oid = regclass_oid(table_name, opts)
1405   ds = metadata_dataset.select{[
1406       pg_attribute[:attname].as(:name),
1407       SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid),
1408       SQL::Cast.new(basetype[:oid], :integer).as(:base_oid),
1409       SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type),
1410       SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type),
1411       SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default),
1412       SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null),
1413       SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key)]}.
1414     from(:pg_class).
1415     join(:pg_attribute, :attrelid=>:oid).
1416     join(:pg_type, :oid=>:atttypid).
1417     left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype).
1418     left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]).
1419     left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true).
1420     where{{pg_attribute[:attisdropped]=>false}}.
1421     where{pg_attribute[:attnum] > 0}.
1422     where{{pg_class[:oid]=>oid}}.
1423     order{pg_attribute[:attnum]}
1424 
1425   if server_version > 100000
1426     ds = ds.select_append{pg_attribute[:attidentity]}
1427 
1428     if server_version > 120000
1429       ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)}
1430     end
1431   end
1432 
1433   ds.map do |row|
1434     row[:default] = nil if blank_object?(row[:default])
1435     if row[:base_oid]
1436       row[:domain_oid] = row[:oid]
1437       row[:oid] = row.delete(:base_oid)
1438       row[:db_domain_type] = row[:db_type]
1439       row[:db_type] = row.delete(:db_base_type)
1440     else
1441       row.delete(:base_oid)
1442       row.delete(:db_base_type)
1443     end
1444     row[:type] = schema_column_type(row[:db_type])
1445     identity = row.delete(:attidentity)
1446     if row[:primary_key]
1447       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
1448     end
1449     [m.call(row.delete(:name)), row]
1450   end
1451 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
1454 def set_transaction_isolation(conn, opts)
1455   level = opts.fetch(:isolation, transaction_isolation_level)
1456   read_only = opts[:read_only]
1457   deferrable = opts[:deferrable]
1458   if level || !read_only.nil? || !deferrable.nil?
1459     sql = String.new
1460     sql << "SET TRANSACTION"
1461     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
1462     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
1463     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
1464     log_connection_execute(conn, sql)
1465   end
1466 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
1469 def sql_function_args(args)
1470   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
1471 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
1474 def supports_combining_alter_table_ops?
1475   true
1476 end
supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1479 def supports_create_or_replace_view?
1480   true
1481 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
1484 def type_literal_generic_bignum_symbol(column)
1485   column[:serial] ? :bigserial : super
1486 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
1489 def type_literal_generic_file(column)
1490   :bytea
1491 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
1494 def type_literal_generic_integer(column)
1495   column[:serial] ? :serial : super
1496 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
1502 def type_literal_generic_string(column)
1503   if column[:fixed]
1504     "char(#{column[:size]||255})"
1505   elsif column[:text] == false or column[:size]
1506     "varchar(#{column[:size]||255})"
1507   else
1508     :text
1509   end
1510 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
1513 def view_with_check_option_support
1514   :local if server_version >= 90400
1515 end