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_trigger(table, name, function, opts=OPTS) click to toggle source

Create a trigger in the database. Arguments:

table

the table on which this trigger operates

name

the name of this trigger

function

the function to call for this trigger, which should return type trigger.

opts

options hash:

:after

Calls the trigger after execution instead of before.

:args

An argument or array of arguments to pass to the function.

:each_row

Calls the trigger for each row instead of for each statement.

:events

Can be :insert, :update, :delete, or an array of any of those. Calls the trigger whenever that type of statement is used. By default, the trigger is called for insert, update, or delete.

:when

A filter to use for the trigger

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

Dataset containing all current database locks

    # File lib/sequel/adapters/shared/postgres.rb
679 def locks
680   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
681 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
689 def notify(channel, opts=OPTS)
690   sql = String.new
691   sql << "NOTIFY "
692   dataset.send(:identifier_append, sql, channel)
693   if payload = opts[:payload]
694     sql << ", "
695     dataset.literal_append(sql, payload.to_s)
696   end
697   execute_ddl(sql, opts)
698 end
primary_key(table, opts=OPTS) click to toggle source

Return primary key for the given table.

    # File lib/sequel/adapters/shared/postgres.rb
701 def primary_key(table, opts=OPTS)
702   quoted_table = quote_schema_table(table)
703   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
704   sql = "#{SELECT_PK_SQL} AND pg_class.oid = #{literal(regclass_oid(table, opts))}"
705   value = fetch(sql).single_value
706   Sequel.synchronize{@primary_keys[quoted_table] = value}
707 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
710 def primary_key_sequence(table, opts=OPTS)
711   quoted_table = quote_schema_table(table)
712   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
713   sql = "#{SELECT_SERIAL_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
714   if pks = fetch(sql).single_record
715     value = literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
716     Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
717   else
718     sql = "#{SELECT_CUSTOM_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
719     if pks = fetch(sql).single_record
720       value = literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
721       Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
722     end
723   end
724 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
732 def refresh_view(name, opts=OPTS)
733   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
734 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
738 def reset_primary_key_sequence(table)
739   return unless seq = primary_key_sequence(table)
740   pk = SQL::Identifier.new(primary_key(table))
741   db = self
742   s, t = schema_and_table(table)
743   table = Sequel.qualify(s, t) if s
744 
745   if server_version >= 100000
746     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq)))
747     increment_by = :seqincrement
748     min_value = :seqmin
749   else
750     seq_ds = metadata_dataset.from(LiteralString.new(seq))
751     increment_by = :increment_by
752     min_value = :min_value
753   end
754 
755   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
756 end
rollback_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
758 def rollback_prepared_transaction(transaction_id, opts=OPTS)
759   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
760 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
764 def serial_primary_key_options
765   auto_increment_key = server_version >= 100002 ? :identity : :serial
766   {:primary_key => true, auto_increment_key => true, :type=>Integer}
767 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
770 def server_version(server=nil)
771   return @server_version if @server_version
772   ds = dataset
773   ds = ds.server(server) if server
774   @server_version ||= ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value rescue 0
775 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
778 def supports_create_table_if_not_exists?
779   server_version >= 90100
780 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
783 def supports_deferrable_constraints?
784   server_version >= 90000
785 end
supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

    # File lib/sequel/adapters/shared/postgres.rb
788 def supports_deferrable_foreign_key_constraints?
789   true
790 end
supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

    # File lib/sequel/adapters/shared/postgres.rb
793 def supports_drop_table_if_exists?
794   true
795 end
supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

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

PostgreSQL supports savepoints

    # File lib/sequel/adapters/shared/postgres.rb
815 def supports_savepoints?
816   true
817 end
supports_transaction_isolation_levels?() click to toggle source

PostgreSQL supports transaction isolation levels

    # File lib/sequel/adapters/shared/postgres.rb
820 def supports_transaction_isolation_levels?
821   true
822 end
supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

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

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
803 def supports_trigger_conditions?
804   server_version >= 90000
805 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
838 def tables(opts=OPTS, &block)
839   pg_class_relname('r', opts, &block)
840 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
844 def type_supported?(type)
845   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
846   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
847   Sequel.synchronize{return @supported_types[type] = supported}
848 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
857 def values(v)
858   @default_dataset.clone(:values=>v)
859 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
869 def views(opts=OPTS)
870   relkind = opts[:materialized] ? 'm' : 'v'
871   pg_class_relname(relkind, opts)
872 end

Private Instance Methods

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

Handle exclusion constraints.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1010 def constraint_definition_sql(constraint)
1011   case constraint[:type]
1012   when :exclude
1013     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
1014     sql = String.new
1015     sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}"
1016     constraint_deferrable_sql_append(sql, constraint[:deferrable])
1017     sql
1018   when :foreign_key, :check
1019     sql = super
1020     if constraint[:not_valid]
1021       sql << " NOT VALID"
1022     end
1023     sql
1024   else
1025     super
1026   end
1027 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
1058 def copy_into_sql(table, opts)
1059   sql = String.new
1060   sql << "COPY #{literal(table)}"
1061   if cols = opts[:columns]
1062     sql << literal(Array(cols))
1063   end
1064   sql << " FROM STDIN"
1065   if opts[:options] || opts[:format]
1066     sql << " ("
1067     sql << "FORMAT #{opts[:format]}" if opts[:format]
1068     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1069     sql << ')'
1070   end
1071   sql
1072 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
1075 def copy_table_sql(table, opts)
1076   if table.is_a?(String)
1077     table
1078   else
1079     if opts[:options] || opts[:format]
1080       options = String.new
1081       options << " ("
1082       options << "FORMAT #{opts[:format]}" if opts[:format]
1083       options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
1084       options << ')'
1085     end
1086     table = if table.is_a?(::Sequel::Dataset)
1087       "(#{table.sql})"
1088     else
1089       literal(table)
1090     end
1091     "COPY #{table} TO STDOUT#{options}"
1092   end
1093 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
1096       def create_function_sql(name, definition, opts=OPTS)
1097         args = opts[:args]
1098         if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)}
1099           returns = opts[:returns] || 'void'
1100         end
1101         language = opts[:language] || 'SQL'
1102         <<-END
1103         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
1104         #{"RETURNS #{returns}" if returns}
1105         LANGUAGE #{language}
1106         #{opts[:behavior].to_s.upcase if opts[:behavior]}
1107         #{'STRICT' if opts[:strict]}
1108         #{'SECURITY DEFINER' if opts[:security_definer]}
1109         #{"COST #{opts[:cost]}" if opts[:cost]}
1110         #{"ROWS #{opts[:rows]}" if opts[:rows]}
1111         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
1112         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
1113         END
1114       end
create_language_sql(name, opts=OPTS) click to toggle source

SQL for creating a procedural language.

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

SQL for creating a schema.

     # File lib/sequel/adapters/shared/postgres.rb
1152 def create_schema_sql(name, opts=OPTS)
1153   "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}"
1154 end
create_table_as_sql(name, sql, options) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1210 def create_table_as_sql(name, sql, options)
1211   result = create_table_prefix_sql name, options
1212   if on_commit = options[:on_commit]
1213     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
1214   end
1215   result += " AS #{sql}"
1216 end
create_table_generator_class() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1218 def create_table_generator_class
1219   Postgres::CreateTableGenerator
1220 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
1157 def create_table_prefix_sql(name, options)
1158   prefix_sql = if options[:temp]
1159     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
1160     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
1161     temporary_table_sql
1162   elsif options[:foreign]
1163     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
1164     'FOREIGN '
1165   elsif options[:unlogged]
1166     'UNLOGGED '
1167   end
1168 
1169   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}"
1170 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
1173 def create_table_sql(name, generator, options)
1174   "#{super}#{create_table_suffix_sql(name, options)}"
1175 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
1179 def create_table_suffix_sql(name, options)
1180   sql = String.new
1181 
1182   if inherits = options[:inherits]
1183     sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
1184   end
1185 
1186   if partition_by = options[:partition_by]
1187     sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
1188   end
1189 
1190   if on_commit = options[:on_commit]
1191     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
1192     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
1193     sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
1194   end
1195 
1196   if tablespace = options[:tablespace]
1197     sql << " TABLESPACE #{quote_identifier(tablespace)}"
1198   end
1199 
1200   if server = options[:foreign]
1201     sql << " SERVER #{quote_identifier(server)}"
1202     if foreign_opts = options[:options]
1203       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
1204     end
1205   end
1206 
1207   sql
1208 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
1223 def create_trigger_sql(table, name, function, opts=OPTS)
1224   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
1225   whence = opts[:after] ? 'AFTER' : 'BEFORE'
1226   if filter = opts[:when]
1227     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
1228     filter = " WHEN #{filter_expr(filter)}"
1229   end
1230   "CREATE TRIGGER #{name} #{whence} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)}#{' FOR EACH ROW' if opts[:each_row]}#{filter} EXECUTE PROCEDURE #{function}(#{Array(opts[:args]).map{|a| literal(a)}.join(', ')})"
1231 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
1234 def create_view_prefix_sql(name, options)
1235   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])
1236 
1237   if tablespace = options[:tablespace]
1238     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1239   end
1240 
1241   sql
1242 end
database_error_regexps() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1053 def database_error_regexps
1054   DATABASE_ERROR_REGEXPS
1055 end
database_specific_error_class_from_sqlstate(sqlstate) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1029 def database_specific_error_class_from_sqlstate(sqlstate)
1030   if sqlstate == '23P01'
1031     ExclusionConstraintViolation
1032   elsif sqlstate == '40P01'
1033     SerializationFailure
1034   elsif sqlstate == '55P03'
1035     DatabaseLockTimeout
1036   else
1037     super
1038   end
1039 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
1245 def drop_function_sql(name, opts=OPTS)
1246   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
1247 end
drop_index_sql(table, op) click to toggle source

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

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

Support :foreign tables

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

Setup datastructures shared by all postgres adapters.

     # File lib/sequel/adapters/shared/postgres.rb
1315 def initialize_postgres_adapter
1316   @primary_keys = {}
1317   @primary_key_sequences = {}
1318   @supported_types = {}
1319   procs = @conversion_procs = CONVERSION_PROCS.dup
1320   procs[1184] = procs[1114] = method(:to_application_timestamp)
1321 end
pg_class_relname(type, opts) { |ds| ... } click to toggle source

Backbone of the tables and views support.

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

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1469 def supports_create_or_replace_view?
1470   true
1471 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
1474 def type_literal_generic_bignum_symbol(column)
1475   column[:serial] ? :bigserial : super
1476 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
1479 def type_literal_generic_file(column)
1480   :bytea
1481 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
1484 def type_literal_generic_integer(column)
1485   column[:serial] ? :serial : super
1486 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
1492 def type_literal_generic_string(column)
1493   if column[:fixed]
1494     "char(#{column[:size]||255})"
1495   elsif column[:text] == false or column[:size]
1496     "varchar(#{column[:size]||255})"
1497   else
1498     :text
1499   end
1500 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
1503 def view_with_check_option_support
1504   :local if server_version >= 90400
1505 end