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
202 def add_conversion_proc(oid, callable=nil, &block)
203   conversion_procs[oid] = callable || block
204 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
209 def add_named_conversion_proc(name, &block)
210   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
211     raise Error, "No matching type in pg_type for #{name.inspect}"
212   end
213   add_conversion_proc(oid, block)
214 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
225 def check_constraints(table)
226   m = output_identifier_meth
227 
228   rows = metadata_dataset.
229     from{pg_constraint.as(:co)}.
230     left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
231     where(:conrelid=>regclass_oid(table), :contype=>'c').
232     select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
233 
234   hash = {}
235   rows.each do |row|
236     constraint = m.call(row[:constraint])
237     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]}
238     entry[:columns] << m.call(row[:column]) if row[:column]
239   end
240   
241   hash
242 end
commit_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
216 def commit_prepared_transaction(transaction_id, opts=OPTS)
217   run("COMMIT PREPARED #{literal(transaction_id)}", opts)
218 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
262 def convert_serial_to_identity(table, opts=OPTS)
263   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
264 
265   server = opts[:server]
266   server_hash = server ? {:server=>server} : OPTS
267   ds = dataset
268   ds = ds.server(server) if server
269 
270   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
271 
272   table_oid = regclass_oid(table)
273   im = input_identifier_meth
274   unless column = im.call(opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
275     raise Error, "could not determine column to convert from serial to identity automatically"
276   end
277 
278   column_num = ds.from(:pg_attribute).
279     where(:attrelid=>table_oid, :attname=>column).
280     get(:attnum)
281 
282   pg_class = Sequel.cast('pg_class', :regclass)
283   res = ds.from(:pg_depend).
284     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
285     select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])
286 
287   case res.length
288   when 0
289     raise Error, "unable to find related sequence when converting serial to identity"
290   when 1
291     seq_oid, already_identity = res.first
292   else
293     raise Error, "more than one linked sequence found when converting serial to identity"
294   end
295 
296   return if already_identity
297 
298   transaction(server_hash) do
299     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash)
300 
301     ds.from(:pg_depend).
302       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
303       update(:deptype=>'i')
304 
305     ds.from(:pg_attribute).
306       where(:attrelid=>table_oid, :attname=>column).
307       update(:attidentity=>'d')
308   end
309 
310   remove_cached_schema(table)
311   nil
312 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
334 def create_function(name, definition, opts=OPTS)
335   self << create_function_sql(name, definition, opts)
336 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
345 def create_language(name, opts=OPTS)
346   self << create_language_sql(name, opts)
347 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
354 def create_schema(name, opts=OPTS)
355   self << create_schema_sql(name, opts)
356 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
369 def create_trigger(table, name, function, opts=OPTS)
370   self << create_trigger_sql(table, name, function, opts)
371 end
database_type() click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
373 def database_type
374   :postgres
375 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
382 def do(code, opts=OPTS)
383   language = opts[:language]
384   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}"
385 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
393 def drop_function(name, opts=OPTS)
394   self << drop_function_sql(name, opts)
395 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
402 def drop_language(name, opts=OPTS)
403   self << drop_language_sql(name, opts)
404 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
411 def drop_schema(name, opts=OPTS)
412   self << drop_schema_sql(name, opts)
413 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
421 def drop_trigger(table, name, opts=OPTS)
422   self << drop_trigger_sql(table, name, opts)
423 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
435 def foreign_key_list(table, opts=OPTS)
436   m = output_identifier_meth
437   schema, _ = opts.fetch(:schema, schema_and_table(table))
438   oid = regclass_oid(table)
439   reverse = opts[:reverse]
440 
441   if reverse
442     ctable = Sequel[:att2]
443     cclass = Sequel[:cl2]
444     rtable = Sequel[:att]
445     rclass = Sequel[:cl]
446   else
447     ctable = Sequel[:att]
448     cclass = Sequel[:cl]
449     rtable = Sequel[:att2]
450     rclass = Sequel[:cl2]
451   end
452 
453   if server_version >= 90500
454     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
455     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
456   else
457     range = 0...32
458     cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
459     rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
460   end
461 
462   ds = metadata_dataset.
463     from{pg_constraint.as(:co)}.
464     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
465     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
466     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
467     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
468     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
469     order{[co[:conname], cpos]}.
470     where{{
471       cl[:relkind]=>'r',
472       co[:contype]=>'f',
473       cl[:oid]=>oid,
474       cpos=>rpos
475     }}.
476     select{[
477       co[:conname].as(:name),
478       ctable[:attname].as(:column),
479       co[:confupdtype].as(:on_update),
480       co[:confdeltype].as(:on_delete),
481       cl2[:relname].as(:table),
482       rtable[:attname].as(:refcolumn),
483       SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
484       nsp[:nspname].as(:schema)
485     ]}
486 
487   if reverse
488     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
489   end
490 
491   h = {}
492   fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
493 
494   ds.each do |row|
495     if reverse
496       key = [row[:schema], row[:table], row[:name]]
497     else
498       key = row[:name]
499     end
500 
501     if r = h[key]
502       r[:columns] << m.call(row[:column])
503       r[:key] << m.call(row[:refcolumn])
504     else
505       entry = h[key] = {
506         :name=>m.call(row[:name]),
507         :columns=>[m.call(row[:column])],
508         :key=>[m.call(row[:refcolumn])],
509         :on_update=>fklod_map[row[:on_update]],
510         :on_delete=>fklod_map[row[:on_delete]],
511         :deferrable=>row[:deferrable],
512         :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
513       }
514 
515       unless schema
516         # If not combining schema information into the :table entry
517         # include it as a separate entry.
518         entry[:schema] = m.call(row[:schema])
519       end
520     end
521   end
522 
523   h.values
524 end
freeze() click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/postgres.rb
526 def freeze
527   server_version
528   supports_prepared_transactions?
529   @conversion_procs.freeze
530   super
531 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
534 def indexes(table, opts=OPTS)
535   m = output_identifier_meth
536   oid = regclass_oid(table, opts)
537 
538   if server_version >= 90500
539     order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
540   else
541     range = 0...32
542     order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
543   end
544 
545   attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey])
546 
547   ds = metadata_dataset.
548     from{pg_class.as(:tab)}.
549     join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
550     join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
551     join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
552     left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
553     where{{
554       indc[:relkind]=>'i',
555       ind[:indisprimary]=>false,
556       :indexprs=>nil,
557       :indisvalid=>true,
558       tab[:oid]=>oid}}.
559     order(*order).
560     select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}
561 
562   ds = ds.where(:indpred=>nil) unless opts[:include_partial]
563   ds = ds.where(:indisready=>true) if server_version >= 80300
564   ds = ds.where(:indislive=>true) if server_version >= 90300
565 
566   indexes = {}
567   ds.each do |r|
568     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
569     i[:columns] << m.call(r[:column])
570   end
571   indexes
572 end
locks() click to toggle source

Dataset containing all current database locks

    # File lib/sequel/adapters/shared/postgres.rb
575 def locks
576   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
577 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
585 def notify(channel, opts=OPTS)
586   sql = String.new
587   sql << "NOTIFY "
588   dataset.send(:identifier_append, sql, channel)
589   if payload = opts[:payload]
590     sql << ", "
591     dataset.literal_append(sql, payload.to_s)
592   end
593   execute_ddl(sql, opts)
594 end
primary_key(table, opts=OPTS) click to toggle source

Return primary key for the given table.

    # File lib/sequel/adapters/shared/postgres.rb
597 def primary_key(table, opts=OPTS)
598   quoted_table = quote_schema_table(table)
599   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
600   sql = "#{SELECT_PK_SQL} AND pg_class.oid = #{literal(regclass_oid(table, opts))}"
601   value = fetch(sql).single_value
602   Sequel.synchronize{@primary_keys[quoted_table] = value}
603 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
606 def primary_key_sequence(table, opts=OPTS)
607   quoted_table = quote_schema_table(table)
608   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
609   sql = "#{SELECT_SERIAL_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
610   if pks = fetch(sql).single_record
611     value = literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
612     Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
613   else
614     sql = "#{SELECT_CUSTOM_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
615     if pks = fetch(sql).single_record
616       value = literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
617       Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
618     end
619   end
620 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
628 def refresh_view(name, opts=OPTS)
629   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
630 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
634 def reset_primary_key_sequence(table)
635   return unless seq = primary_key_sequence(table)
636   pk = SQL::Identifier.new(primary_key(table))
637   db = self
638   s, t = schema_and_table(table)
639   table = Sequel.qualify(s, t) if s
640 
641   if server_version >= 100000
642     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq)))
643     increment_by = :seqincrement
644     min_value = :seqmin
645   else
646     seq_ds = metadata_dataset.from(LiteralString.new(seq))
647     increment_by = :increment_by
648     min_value = :min_value
649   end
650 
651   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
652 end
rollback_prepared_transaction(transaction_id, opts=OPTS) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
654 def rollback_prepared_transaction(transaction_id, opts=OPTS)
655   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
656 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
660 def serial_primary_key_options
661   auto_increment_key = server_version >= 100002 ? :identity : :serial
662   {:primary_key => true, auto_increment_key => true, :type=>Integer}
663 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
666 def server_version(server=nil)
667   return @server_version if @server_version
668   ds = dataset
669   ds = ds.server(server) if server
670   @server_version ||= ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value rescue 0
671 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
674 def supports_create_table_if_not_exists?
675   server_version >= 90100
676 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
679 def supports_deferrable_constraints?
680   server_version >= 90000
681 end
supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

    # File lib/sequel/adapters/shared/postgres.rb
684 def supports_deferrable_foreign_key_constraints?
685   true
686 end
supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

    # File lib/sequel/adapters/shared/postgres.rb
689 def supports_drop_table_if_exists?
690   true
691 end
supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

    # File lib/sequel/adapters/shared/postgres.rb
694 def supports_partial_indexes?
695   true
696 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
705 def supports_prepared_transactions?
706   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
707   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
708 end
supports_savepoints?() click to toggle source

PostgreSQL supports savepoints

    # File lib/sequel/adapters/shared/postgres.rb
711 def supports_savepoints?
712   true
713 end
supports_transaction_isolation_levels?() click to toggle source

PostgreSQL supports transaction isolation levels

    # File lib/sequel/adapters/shared/postgres.rb
716 def supports_transaction_isolation_levels?
717   true
718 end
supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

    # File lib/sequel/adapters/shared/postgres.rb
721 def supports_transactional_ddl?
722   true
723 end
supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
699 def supports_trigger_conditions?
700   server_version >= 90000
701 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
734 def tables(opts=OPTS, &block)
735   pg_class_relname('r', opts, &block)
736 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
740 def type_supported?(type)
741   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
742   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
743   Sequel.synchronize{return @supported_types[type] = supported}
744 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
753 def values(v)
754   @default_dataset.clone(:values=>v)
755 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
765 def views(opts=OPTS)
766   relkind = opts[:materialized] ? 'm' : 'v'
767   pg_class_relname(relkind, opts)
768 end

Private Instance Methods

alter_table_add_column_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
772 def alter_table_add_column_sql(table, op)
773   "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}"
774 end
alter_table_drop_column_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
790 def alter_table_drop_column_sql(table, op)
791   "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}"
792 end
alter_table_generator_class() click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
776 def alter_table_generator_class
777   Postgres::AlterTableGenerator
778 end
alter_table_set_column_type_sql(table, op) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/postgres.rb
780 def alter_table_set_column_type_sql(table, op)
781   s = super
782   if using = op[:using]
783     using = Sequel::LiteralString.new(using) if using.is_a?(String)
784     s += ' USING '
785     s << literal(using)
786   end
787   s
788 end
alter_table_validate_constraint_sql(table, op) click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
794 def alter_table_validate_constraint_sql(table, op)
795   "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
796 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
801 def begin_new_transaction(conn, opts)
802   super
803   if opts.has_key?(:synchronous)
804     case sync = opts[:synchronous]
805     when true
806       sync = :on
807     when false
808       sync = :off
809     when nil
810       return
811     end
812 
813     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
814   end
815 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
818 def begin_savepoint(conn, opts)
819   super
820 
821   unless (read_only = opts[:read_only]).nil?
822     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
823   end
824 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
828 def column_definition_collate_sql(sql, column)
829   if collate = column[:collate]
830     collate = literal(collate) unless collate.is_a?(String)
831     sql << " COLLATE #{collate}"
832   end
833 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
837 def column_definition_default_sql(sql, column)
838   super
839   if !column[:serial] && !['serial', 'bigserial'].include?(column[:type].to_s) && !column[:default] && (identity = column[:identity])
840     sql << " GENERATED "
841     sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
842     sql << " AS IDENTITY"
843   end
844 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
847 def column_schema_normalize_default(default, type)
848   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
849     default = m[1] || m[2]
850   end
851   super(default, type)
852 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
866 def combinable_alter_table_op?(op)
867   (super || op[:op] == :validate_constraint) && op[:op] != :rename_column
868 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
856 def commit_transaction(conn, opts=OPTS)
857   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
858     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
859   else
860     super
861   end
862 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
872 def connection_configuration_sqls(opts=@opts)
873   sqls = []
874 
875   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
876 
877   cmm = opts.fetch(:client_min_messages, :warning)
878   if cmm && !cmm.to_s.empty?
879     cmm = cmm.to_s.upcase.strip
880     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
881       raise Error, "Unsupported client_min_messages setting: #{cmm}"
882     end
883     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
884   end
885 
886   if search_path = opts[:search_path]
887     case search_path
888     when String
889       search_path = search_path.split(",").map(&:strip)
890     when Array
891       # nil
892     else
893       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
894     end
895     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
896   end
897 
898   sqls
899 end
constraint_definition_sql(constraint) click to toggle source

Handle exclusion constraints.

Calls superclass method
    # File lib/sequel/adapters/shared/postgres.rb
902 def constraint_definition_sql(constraint)
903   case constraint[:type]
904   when :exclude
905     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
906     sql = String.new
907     sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}"
908     constraint_deferrable_sql_append(sql, constraint[:deferrable])
909     sql
910   when :foreign_key, :check
911     sql = super
912     if constraint[:not_valid]
913       sql << " NOT VALID"
914     end
915     sql
916   else
917     super
918   end
919 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
950 def copy_into_sql(table, opts)
951   sql = String.new
952   sql << "COPY #{literal(table)}"
953   if cols = opts[:columns]
954     sql << literal(Array(cols))
955   end
956   sql << " FROM STDIN"
957   if opts[:options] || opts[:format]
958     sql << " ("
959     sql << "FORMAT #{opts[:format]}" if opts[:format]
960     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
961     sql << ')'
962   end
963   sql
964 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
967 def copy_table_sql(table, opts)
968   if table.is_a?(String)
969     table
970   else
971     if opts[:options] || opts[:format]
972       options = String.new
973       options << " ("
974       options << "FORMAT #{opts[:format]}" if opts[:format]
975       options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
976       options << ')'
977     end
978     table = if table.is_a?(::Sequel::Dataset)
979       "(#{table.sql})"
980     else
981       literal(table)
982     end
983     "COPY #{table} TO STDOUT#{options}"
984   end
985 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
 988       def create_function_sql(name, definition, opts=OPTS)
 989         args = opts[:args]
 990         if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)}
 991           returns = opts[:returns] || 'void'
 992         end
 993         language = opts[:language] || 'SQL'
 994         <<-END
 995         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
 996         #{"RETURNS #{returns}" if returns}
 997         LANGUAGE #{language}
 998         #{opts[:behavior].to_s.upcase if opts[:behavior]}
 999         #{'STRICT' if opts[:strict]}
1000         #{'SECURITY DEFINER' if opts[:security_definer]}
1001         #{"COST #{opts[:cost]}" if opts[:cost]}
1002         #{"ROWS #{opts[:rows]}" if opts[:rows]}
1003         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
1004         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
1005         END
1006       end
create_language_sql(name, opts=OPTS) click to toggle source

SQL for creating a procedural language.

     # File lib/sequel/adapters/shared/postgres.rb
1009 def create_language_sql(name, opts=OPTS)
1010   "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]}"
1011 end
create_schema_sql(name, opts=OPTS) click to toggle source

SQL for creating a schema.

     # File lib/sequel/adapters/shared/postgres.rb
1014 def create_schema_sql(name, opts=OPTS)
1015   "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}"
1016 end
create_table_as_sql(name, sql, options) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1061 def create_table_as_sql(name, sql, options)
1062   result = create_table_prefix_sql name, options
1063   if on_commit = options[:on_commit]
1064     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
1065   end
1066   result += " AS #{sql}"
1067 end
create_table_generator_class() click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1069 def create_table_generator_class
1070   Postgres::CreateTableGenerator
1071 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
1019 def create_table_prefix_sql(name, options)
1020   prefix_sql = if options[:temp]
1021     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
1022     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
1023     temporary_table_sql
1024   elsif options[:foreign]
1025     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
1026     'FOREIGN '
1027   elsif options[:unlogged]
1028     'UNLOGGED '
1029   end
1030 
1031   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}"
1032 end
create_table_sql(name, generator, options) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1034 def create_table_sql(name, generator, options)
1035   sql = super
1036 
1037   if inherits = options[:inherits]
1038     sql += " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
1039   end
1040 
1041   if on_commit = options[:on_commit]
1042     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
1043     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
1044     sql += " ON COMMIT #{ON_COMMIT[on_commit]}"
1045   end
1046 
1047   if tablespace = options[:tablespace]
1048     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1049   end
1050 
1051   if server = options[:foreign]
1052     sql += " SERVER #{quote_identifier(server)}"
1053     if foreign_opts = options[:options]
1054       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
1055     end
1056   end
1057 
1058   sql
1059 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
1074 def create_trigger_sql(table, name, function, opts=OPTS)
1075   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
1076   whence = opts[:after] ? 'AFTER' : 'BEFORE'
1077   if filter = opts[:when]
1078     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
1079     filter = " WHEN #{filter_expr(filter)}"
1080   end
1081   "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(', ')})"
1082 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
1085 def create_view_prefix_sql(name, options)
1086   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])
1087 
1088   if tablespace = options[:tablespace]
1089     sql += " TABLESPACE #{quote_identifier(tablespace)}"
1090   end
1091 
1092   sql
1093 end
database_error_regexps() click to toggle source
    # File lib/sequel/adapters/shared/postgres.rb
945 def database_error_regexps
946   DATABASE_ERROR_REGEXPS
947 end
database_specific_error_class_from_sqlstate(sqlstate) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/postgres.rb
921 def database_specific_error_class_from_sqlstate(sqlstate)
922   if sqlstate == '23P01'
923     ExclusionConstraintViolation
924   elsif sqlstate == '40P01'
925     SerializationFailure
926   elsif sqlstate == '55P03'
927     DatabaseLockTimeout
928   else
929     super
930   end
931 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
1096 def drop_function_sql(name, opts=OPTS)
1097   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
1098 end
drop_index_sql(table, op) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1101 def drop_index_sql(table, op)
1102   sch, _ = schema_and_table(table)
1103   "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]}"
1104 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
1107 def drop_language_sql(name, opts=OPTS)
1108   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1109 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
1112 def drop_schema_sql(name, opts=OPTS)
1113   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1114 end
drop_table_sql(name, options) click to toggle source

Support :foreign tables

     # File lib/sequel/adapters/shared/postgres.rb
1122 def drop_table_sql(name, options)
1123   "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
1124 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
1117 def drop_trigger_sql(table, name, opts=OPTS)
1118   "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
1119 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
1127 def drop_view_sql(name, opts=OPTS)
1128   "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
1129 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
1133 def filter_schema(ds, opts)
1134   expr = if schema = opts[:schema]
1135     schema.to_s
1136   else
1137     Sequel.function(:any, Sequel.function(:current_schemas, false))
1138   end
1139   ds.where{{pg_namespace[:nspname]=>expr}}
1140 end
index_definition_sql(table_name, index) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1142 def index_definition_sql(table_name, index)
1143   cols = index[:columns]
1144   index_name = index[:name] || default_index_name(table_name, cols)
1145   expr = if o = index[:opclass]
1146     "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
1147   else
1148     literal(Array(cols))
1149   end
1150   if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
1151   unique = "UNIQUE " if index[:unique]
1152   index_type = index[:type]
1153   filter = index[:where] || index[:filter]
1154   filter = " WHERE #{filter_expr(filter)}" if filter
1155   case index_type
1156   when :full_text
1157     expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
1158     index_type = index[:index_type] || :gin
1159   when :spatial
1160     index_type = :gist
1161   end
1162   "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}"
1163 end
initialize_postgres_adapter() click to toggle source

Setup datastructures shared by all postgres adapters.

     # File lib/sequel/adapters/shared/postgres.rb
1166 def initialize_postgres_adapter
1167   @primary_keys = {}
1168   @primary_key_sequences = {}
1169   @supported_types = {}
1170   procs = @conversion_procs = CONVERSION_PROCS.dup
1171   procs[1184] = procs[1114] = method(:to_application_timestamp)
1172 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
1175 def pg_class_relname(type, opts)
1176   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
1177   ds = filter_schema(ds, opts)
1178   m = output_identifier_meth
1179   if block_given?
1180     yield(ds)
1181   elsif opts[:qualify]
1182     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
1183   else
1184     ds.map{|r| m.call(r[:relname])}
1185   end
1186 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
1189 def prepared_arg_placeholder
1190   PREPARED_ARG_PLACEHOLDER
1191 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
1195 def regclass_oid(expr, opts=OPTS)
1196   if expr.is_a?(String) && !expr.is_a?(LiteralString)
1197     expr = Sequel.identifier(expr)
1198   end
1199 
1200   sch, table = schema_and_table(expr)
1201   sch ||= opts[:schema]
1202   if sch
1203     expr = Sequel.qualify(sch, table)
1204   end
1205   
1206   expr = if ds = opts[:dataset]
1207     ds.literal(expr)
1208   else
1209     literal(expr)
1210   end
1211 
1212   Sequel.cast(expr.to_s,:regclass).cast(:oid)
1213 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
1216 def remove_cached_schema(table)
1217   tab = quote_schema_table(table)
1218   Sequel.synchronize do
1219     @primary_keys.delete(tab)
1220     @primary_key_sequences.delete(tab)
1221   end
1222   super
1223 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
1227 def rename_table_sql(name, new_name)
1228   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
1229 end
schema_column_type(db_type) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1231 def schema_column_type(db_type)
1232   case db_type
1233   when /\Ainterval\z/io
1234     :interval
1235   when /\Acitext\z/io
1236     :string
1237   else
1238     super
1239   end
1240 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
1243 def schema_parse_table(table_name, opts)
1244   m = output_identifier_meth(opts[:dataset])
1245   oid = regclass_oid(table_name, opts)
1246   ds = metadata_dataset.select{[
1247       pg_attribute[:attname].as(:name),
1248       SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid),
1249       SQL::Cast.new(basetype[:oid], :integer).as(:base_oid),
1250       SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type),
1251       SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type),
1252       SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default),
1253       SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null),
1254       SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key)]}.
1255     from(:pg_class).
1256     join(:pg_attribute, :attrelid=>:oid).
1257     join(:pg_type, :oid=>:atttypid).
1258     left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype).
1259     left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]).
1260     left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true).
1261     where{{pg_attribute[:attisdropped]=>false}}.
1262     where{pg_attribute[:attnum] > 0}.
1263     where{{pg_class[:oid]=>oid}}.
1264     order{pg_attribute[:attnum]}
1265 
1266   if server_version > 100000
1267     ds = ds.select_append{pg_attribute[:attidentity]}
1268   end
1269 
1270   ds.map do |row|
1271     row[:default] = nil if blank_object?(row[:default])
1272     if row[:base_oid]
1273       row[:domain_oid] = row[:oid]
1274       row[:oid] = row.delete(:base_oid)
1275       row[:db_domain_type] = row[:db_type]
1276       row[:db_type] = row.delete(:db_base_type)
1277     else
1278       row.delete(:base_oid)
1279       row.delete(:db_base_type)
1280     end
1281     row[:type] = schema_column_type(row[:db_type])
1282     identity = row.delete(:attidentity)
1283     if row[:primary_key]
1284       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
1285     end
1286     [m.call(row.delete(:name)), row]
1287   end
1288 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
1291 def set_transaction_isolation(conn, opts)
1292   level = opts.fetch(:isolation, transaction_isolation_level)
1293   read_only = opts[:read_only]
1294   deferrable = opts[:deferrable]
1295   if level || !read_only.nil? || !deferrable.nil?
1296     sql = String.new
1297     sql << "SET TRANSACTION"
1298     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
1299     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
1300     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
1301     log_connection_execute(conn, sql)
1302   end
1303 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
1306 def sql_function_args(args)
1307   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
1308 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
1311 def supports_combining_alter_table_ops?
1312   true
1313 end
supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1316 def supports_create_or_replace_view?
1317   true
1318 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
1321 def type_literal_generic_bignum_symbol(column)
1322   column[:serial] ? :bigserial : super
1323 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
1326 def type_literal_generic_file(column)
1327   :bytea
1328 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
1331 def type_literal_generic_integer(column)
1332   column[:serial] ? :serial : super
1333 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
1339 def type_literal_generic_string(column)
1340   if column[:fixed]
1341     "char(#{column[:size]||255})"
1342   elsif column[:text] == false or column[:size]
1343     "varchar(#{column[:size]||255})"
1344   else
1345     :text
1346   end
1347 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
1350 def view_with_check_option_support
1351   :local if server_version >= 90400
1352 end