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.

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

Dataset containing all current database locks

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

Return primary key for the given table.

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

PostgreSQL supports deferrable foreign key constraints.

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

PostgreSQL supports DROP TABLE IF EXISTS

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

PostgreSQL supports partial indexes.

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

PostgreSQL supports savepoints

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

PostgreSQL supports transaction isolation levels

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

PostgreSQL supports transaction DDL statements.

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

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
815 def supports_trigger_conditions?
816   server_version >= 90000
817 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
850 def tables(opts=OPTS, &block)
851   pg_class_relname(['r', 'p'], opts, &block)
852 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
856 def type_supported?(type)
857   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
858   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
859   Sequel.synchronize{return @supported_types[type] = supported}
860 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
869 def values(v)
870   @default_dataset.clone(:values=>v)
871 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
881 def views(opts=OPTS)
882   relkind = opts[:materialized] ? 'm' : 'v'
883   pg_class_relname(relkind, opts)
884 end

Private Instance Methods

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

Handle exclusion constraints.

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

SQL for creating a procedural language.

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

SQL for creating a schema.

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

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

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

Support :foreign tables

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

Setup datastructures shared by all postgres adapters.

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

Backbone of the tables and views support.

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

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1482 def supports_create_or_replace_view?
1483   true
1484 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
1487 def type_literal_generic_bignum_symbol(column)
1488   column[:serial] ? :bigserial : super
1489 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
1492 def type_literal_generic_file(column)
1493   :bytea
1494 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
1497 def type_literal_generic_integer(column)
1498   column[:serial] ? :serial : super
1499 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
1505 def type_literal_generic_string(column)
1506   if column[:text]
1507     :text
1508   elsif column[:fixed]
1509     "char(#{column[:size]||default_string_column_size})"
1510   elsif column[:text] == false || column[:size]
1511     "varchar(#{column[:size]||default_string_column_size})"
1512   else
1513     :text
1514   end
1515 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
1518 def view_with_check_option_support
1519   :local if server_version >= 90400
1520 end