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

Dataset containing all current database locks

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

Return primary key for the given table.

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

PostgreSQL supports deferrable foreign key constraints.

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

PostgreSQL supports DROP TABLE IF EXISTS

    # File lib/sequel/adapters/shared/postgres.rb
693 def supports_drop_table_if_exists?
694   true
695 end
supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

    # File lib/sequel/adapters/shared/postgres.rb
698 def supports_partial_indexes?
699   true
700 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
709 def supports_prepared_transactions?
710   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
711   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
712 end
supports_savepoints?() click to toggle source

PostgreSQL supports savepoints

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

PostgreSQL supports transaction isolation levels

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

PostgreSQL supports transaction DDL statements.

    # File lib/sequel/adapters/shared/postgres.rb
725 def supports_transactional_ddl?
726   true
727 end
supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

    # File lib/sequel/adapters/shared/postgres.rb
703 def supports_trigger_conditions?
704   server_version >= 90000
705 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
738 def tables(opts=OPTS, &block)
739   pg_class_relname('r', opts, &block)
740 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
744 def type_supported?(type)
745   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
746   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
747   Sequel.synchronize{return @supported_types[type] = supported}
748 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
757 def values(v)
758   @default_dataset.clone(:values=>v)
759 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
769 def views(opts=OPTS)
770   relkind = opts[:materialized] ? 'm' : 'v'
771   pg_class_relname(relkind, opts)
772 end

Private Instance Methods

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

Handle exclusion constraints.

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

SQL for creating a procedural language.

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

SQL for creating a schema.

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

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

     # File lib/sequel/adapters/shared/postgres.rb
1109 def drop_index_sql(table, op)
1110   sch, _ = schema_and_table(table)
1111   "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]}"
1112 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
1115 def drop_language_sql(name, opts=OPTS)
1116   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
1117 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
1120 def drop_schema_sql(name, opts=OPTS)
1121   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
1122 end
drop_table_sql(name, options) click to toggle source

Support :foreign tables

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

Setup datastructures shared by all postgres adapters.

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

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
1324 def supports_create_or_replace_view?
1325   true
1326 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
1329 def type_literal_generic_bignum_symbol(column)
1330   column[:serial] ? :bigserial : super
1331 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
1334 def type_literal_generic_file(column)
1335   :bytea
1336 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
1339 def type_literal_generic_integer(column)
1340   column[:serial] ? :serial : super
1341 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
1347 def type_literal_generic_string(column)
1348   if column[:fixed]
1349     "char(#{column[:size]||255})"
1350   elsif column[:text] == false or column[:size]
1351     "varchar(#{column[:size]||255})"
1352   else
1353     :text
1354   end
1355 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
1358 def view_with_check_option_support
1359   :local if server_version >= 90400
1360 end