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.

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

Dataset containing all current database locks

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

Return primary key for the given table.

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

PostgreSQL supports deferrable foreign key constraints.

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

PostgreSQL supports DROP TABLE IF EXISTS

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

PostgreSQL supports partial indexes.

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

PostgreSQL supports savepoints

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

PostgreSQL supports transaction isolation levels

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

PostgreSQL supports transaction DDL statements.

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

PostgreSQL 9.0+ supports trigger conditions.

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

Private Instance Methods

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

Handle exclusion constraints.

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

SQL for creating a procedural language.

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

SQL for creating a schema.

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

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

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

Support :foreign tables

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

Setup datastructures shared by all postgres adapters.

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

Backbone of the tables and views support.

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

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1480 def supports_create_or_replace_view?
1481   true
1482 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
1485 def type_literal_generic_bignum_symbol(column)
1486   column[:serial] ? :bigserial : super
1487 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
1490 def type_literal_generic_file(column)
1491   :bytea
1492 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
1495 def type_literal_generic_integer(column)
1496   column[:serial] ? :serial : super
1497 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
1503 def type_literal_generic_string(column)
1504   if column[:text]
1505     :text
1506   elsif column[:fixed]
1507     "char(#{column[:size]||default_string_column_size})"
1508   elsif column[:text] == false || column[:size]
1509     "varchar(#{column[:size]||default_string_column_size})"
1510   else
1511     :text
1512   end
1513 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
1516 def view_with_check_option_support
1517   :local if server_version >= 90400
1518 end