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=Proc.new) 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
198 def add_conversion_proc(oid, callable=Proc.new)
199   conversion_procs[oid] = callable
200 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
205 def add_named_conversion_proc(name, &block)
206   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
207     raise Error, "No matching type in pg_type for #{name.inspect}"
208   end
209   add_conversion_proc(oid, block)
210 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

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

Dataset containing all current database locks

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

Return primary key for the given table.

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

PostgreSQL supports deferrable foreign key constraints.

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

PostgreSQL supports DROP TABLE IF EXISTS

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

PostgreSQL supports partial indexes.

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

PostgreSQL supports savepoints

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

PostgreSQL supports transaction isolation levels

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

PostgreSQL supports transaction DDL statements.

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

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
697 def supports_trigger_conditions?
698   server_version >= 90000
699 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
732 def tables(opts=OPTS, &block)
733   pg_class_relname('r', opts, &block)
734 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
738 def type_supported?(type)
739   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
740   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
741   Sequel.synchronize{return @supported_types[type] = supported}
742 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
751 def values(v)
752   @default_dataset.clone(:values=>v)
753 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
763 def views(opts=OPTS)
764   relkind = opts[:materialized] ? 'm' : 'v'
765   pg_class_relname(relkind, opts)
766 end

Private Instance Methods

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

Handle exclusion constraints.

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

SQL for creating a procedural language.

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

SQL for creating a schema.

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

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

     # File lib/sequel/adapters/shared/postgres.rb
1089 def drop_index_sql(table, op)
1090   sch, _ = schema_and_table(table)
1091   "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]}"
1092 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
1095 def drop_language_sql(name, opts=OPTS)
1096   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1097 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
1100 def drop_schema_sql(name, opts=OPTS)
1101   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1102 end
drop_table_sql(name, options) click to toggle source

Support :foreign tables

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

Setup datastructures shared by all postgres adapters.

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

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1304 def supports_create_or_replace_view?
1305   true
1306 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
1309 def type_literal_generic_bignum_symbol(column)
1310   column[:serial] ? :bigserial : super
1311 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
1314 def type_literal_generic_file(column)
1315   :bytea
1316 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
1319 def type_literal_generic_integer(column)
1320   column[:serial] ? :serial : super
1321 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
1327 def type_literal_generic_string(column)
1328   if column[:fixed]
1329     "char(#{column[:size]||255})"
1330   elsif column[:text] == false or column[:size]
1331     "varchar(#{column[:size]||255})"
1332   else
1333     :text
1334   end
1335 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
1338 def view_with_check_option_support
1339   :local if server_version >= 90400
1340 end