module Sequel::Postgres::DatabaseMethods

  1. lib/sequel/adapters/shared/postgres.rb


DATABASE_ERROR_REGEXPS = [ # Add this check first, since otherwise it's possible for users to control # which exception class is generated. [/invalid input syntax/, DatabaseError], [/duplicate key value violates unique constraint/, UniqueConstraintViolation], [/violates foreign key constraint/, ForeignKeyConstraintViolation], [/violates check constraint/, CheckConstraintViolation], [/violates not-null constraint/, NotNullConstraintViolation], [/conflicting key value violates exclusion constraint/, ExclusionConstraintViolation], [/could not serialize access/, SerializationFailure], [/could not obtain lock on row in relation/, DatabaseLockTimeout], ].freeze  
FOREIGN_KEY_LIST_ON_DELETE_MAP = {'a'=>:no_action, 'r'=>:restrict, 'c'=>:cascade, 'n'=>:set_null, 'd'=>:set_default}.freeze  
ON_COMMIT = {:drop => 'DROP', :delete_rows => 'DELETE ROWS', :preserve_rows => 'PRESERVE ROWS'}.freeze  
SELECT_CUSTOM_SEQUENCE_SQL = (<<-end_sql SELECT name.nspname AS "schema", CASE WHEN split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2) ~ '.' THEN substr(split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2), strpos(split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2), '.')+1) ELSE split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2) END AS "sequence" FROM pg_class t JOIN pg_namespace name ON (t.relnamespace = name.oid) JOIN pg_attribute attr ON (t.oid = attrelid) JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum) JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1]) WHERE cons.contype = 'p' AND pg_get_expr(def.adbin, attr.attrelid) ~* 'nextval' end_sql ).strip.gsub(/\s+/, ' ').freeze  

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 = (<<-end_sql SELECT pg_attribute.attname AS pk FROM pg_class, pg_attribute, pg_index, pg_namespace WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.relnamespace = pg_namespace.oid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't' end_sql ).strip.gsub(/\s+/, ' ').freeze  

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 = (<<-end_sql SELECT name.nspname AS "schema", seq.relname AS "sequence" FROM pg_class seq, pg_attribute attr, pg_depend dep, pg_namespace name, pg_constraint cons, pg_class t WHERE seq.oid = dep.objid AND seq.relnamespace = name.oid AND seq.relkind = 'S' AND attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid AND attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] AND attr.attrelid = t.oid AND cons.contype = 'p' end_sql ).strip.gsub(/\s+/, ' ').freeze  

SQL fragment for getting sequence associated with table's primary key, assuming it was a serial primary key column.



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)

Set a conversion proc for the given oid. The callable can be passed either as a argument or a block.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
296 def add_conversion_proc(oid, callable=nil, &block)
297   conversion_procs[oid] = callable || block
298 end
add_named_conversion_proc(name, &block)

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.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
303 def add_named_conversion_proc(name, &block)
304   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
305     raise Error, "No matching type in pg_type for #{name.inspect}"
306   end
307   add_conversion_proc(oid, block)
308 end

A hash of metadata for CHECK constraints on the table. Keys are CHECK constraint name symbols. Values are hashes with the following keys:


An SQL fragment for the definition of the constraint


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.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
319 def check_constraints(table)
320   m = output_identifier_meth
322   rows = metadata_dataset.
323     from{}.
324     left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>, Sequel[:co][:conkey])).
325     where(:conrelid=>regclass_oid(table), :contype=>'c').
326     select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
328   hash = {}
329   rows.each do |row|
330     constraint =[:constraint])
331     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]}
332     entry[:columns] <<[:column]) if row[:column]
333   end
335   hash
336 end
commit_prepared_transaction(transaction_id, opts=OPTS)
[show source]
    # File lib/sequel/adapters/shared/postgres.rb
310 def commit_prepared_transaction(transaction_id, opts=OPTS)
311   run("COMMIT PREPARED #{literal(transaction_id)}", opts)
312 end
convert_serial_to_identity(table, opts=OPTS)

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)



Specify the column to convert instead of using the first primary key column


Run the SQL on the given server

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
356 def convert_serial_to_identity(table, opts=OPTS)
357   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
359   server = opts[:server]
360   server_hash = server ? {:server=>server} : OPTS
361   ds = dataset
362   ds = ds.server(server) if server
364   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
366   table_oid = regclass_oid(table)
367   im = input_identifier_meth
368   unless column =[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
369     raise Error, "could not determine column to convert from serial to identity automatically"
370   end
372   column_num = ds.from(:pg_attribute).
373     where(:attrelid=>table_oid, :attname=>column).
374     get(:attnum)
376   pg_class = Sequel.cast('pg_class', :regclass)
377   res = ds.from(:pg_depend).
378     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
379     select_map([:objid,{:deptype=>'i'}, :v)])
381   case res.length
382   when 0
383     raise Error, "unable to find related sequence when converting serial to identity"
384   when 1
385     seq_oid, already_identity = res.first
386   else
387     raise Error, "more than one linked sequence found when converting serial to identity"
388   end
390   return if already_identity
392   transaction(server_hash) do
393     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash)
395     ds.from(:pg_depend).
396       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
397       update(:deptype=>'i')
399     ds.from(:pg_attribute).
400       where(:attrelid=>table_oid, :attname=>column).
401       update(:attidentity=>'d')
402   end
404   remove_cached_schema(table)
405   nil
406 end
create_function(name, definition, opts=OPTS)

Creates the function in the database. Arguments:


name of the function to create


string definition of the function, or object file for a dynamically loaded C function.


options hash:


function arguments, can be either a symbol or string specifying a type or an array of 1-3 elements:


argument data type


argument name


argument mode (e.g. in, out, inout)


Should be IMMUTABLE, STABLE, or VOLATILE. PostgreSQL assumes VOLATILE by default.


The estimated cost of the function, used by the query planner.


The language the function uses. SQL is the default.


For a dynamically loaded see function, the function's link symbol if different from the definition argument.


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.


The estimated number of rows the function will return. Only use if the function returns SETOF something.


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.


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.


Makes the function return NULL when any argument is NULL.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
428 def create_function(name, definition, opts=OPTS)
429   self << create_function_sql(name, definition, opts)
430 end
create_language(name, opts=OPTS)

Create the procedural language in the database. Arguments:


Name of the procedural language (e.g. plpgsql)


options hash:


The name of a previously registered function used as a call handler for this language.


Replace the installed language if it already exists (on PostgreSQL 9.0+).


Marks the language being created as trusted, allowing unprivileged users to create functions using this language.


The name of previously registered function used as a validator of functions defined in this language.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
439 def create_language(name, opts=OPTS)
440   self << create_language_sql(name, opts)
441 end
create_schema(name, opts=OPTS)

Create a schema in the database. Arguments:


Name of the schema (e.g. admin)


options hash:


Don't raise an error if the schema already exists (PostgreSQL 9.3+)


The owner to set for the schema (defaults to current user if not specified)

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
448 def create_schema(name, opts=OPTS)
449   self << create_schema_sql(name, opts)
450 end
create_table(name, options=OPTS, &block)

Support partitions of tables using the :partition_of option.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
453 def create_table(name, options=OPTS, &block)
454   if options[:partition_of]
455     create_partition_of_table_from_generator(name,, options)
456     return
457   end
459   super
460 end
create_table?(name, options=OPTS, &block)

Support partitions of tables using the :partition_of option.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
463 def create_table?(name, options=OPTS, &block)
464   if options[:partition_of]
465     create_table(name, options.merge!(:if_not_exists=>true), &block)
466     return
467   end
469   super
470 end
create_trigger(table, name, function, opts=OPTS)

Create a trigger in the database. Arguments:


the table on which this trigger operates


the name of this trigger


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


options hash:


Calls the trigger after execution instead of before.


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


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


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.


A filter to use for the trigger

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
483 def create_trigger(table, name, function, opts=OPTS)
484   self << create_trigger_sql(table, name, function, opts)
485 end
[show source]
    # File lib/sequel/adapters/shared/postgres.rb
487 def database_type
488   :postgres
489 end
do(code, opts=OPTS)

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:


The procedural language the code is written in. The PostgreSQL default is plpgsql. Can be specified as a string or a symbol.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
496 def do(code, opts=OPTS)
497   language = opts[:language]
498   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}"
499 end
drop_function(name, opts=OPTS)

Drops the function from the database. Arguments:


name of the function to drop


options hash:


The arguments for the function. See create_function_sql.


Drop other objects depending on this function.


Don't raise an error if the function doesn't exist.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
507 def drop_function(name, opts=OPTS)
508   self << drop_function_sql(name, opts)
509 end
drop_language(name, opts=OPTS)

Drops a procedural language from the database. Arguments:


name of the procedural language to drop


options hash:


Drop other objects depending on this function.


Don't raise an error if the function doesn't exist.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
516 def drop_language(name, opts=OPTS)
517   self << drop_language_sql(name, opts)
518 end
drop_schema(name, opts=OPTS)

Drops a schema from the database. Arguments:


name of the schema to drop


options hash:


Drop all objects in this schema.


Don't raise an error if the schema doesn't exist.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
525 def drop_schema(name, opts=OPTS)
526   self << drop_schema_sql(name, opts)
527 end
drop_trigger(table, name, opts=OPTS)

Drops a trigger from the database. Arguments:


table from which to drop the trigger


name of the trigger to drop


options hash:


Drop other objects depending on this function.


Don't raise an error if the function doesn't exist.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
535 def drop_trigger(table, name, opts=OPTS)
536   self << drop_trigger_sql(table, name, opts)
537 end
foreign_key_list(table, opts=OPTS)

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:


Instead of returning foreign keys in the current table, return foreign keys in other tables that reference the current table.


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.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
549 def foreign_key_list(table, opts=OPTS)
550   m = output_identifier_meth
551   schema, _ = opts.fetch(:schema, schema_and_table(table))
552   oid = regclass_oid(table)
553   reverse = opts[:reverse]
555   if reverse
556     ctable = Sequel[:att2]
557     cclass = Sequel[:cl2]
558     rtable = Sequel[:att]
559     rclass = Sequel[:cl]
560   else
561     ctable = Sequel[:att]
562     cclass = Sequel[:cl]
563     rtable = Sequel[:att2]
564     rclass = Sequel[:cl2]
565   end
567   if server_version >= 90500
568     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
569     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
570   else
571     range = 0...32
572     cpos = Sequel.expr{{|x| [[:conkey], [x]), x]}, 32, ctable[:attnum])}
573     rpos = Sequel.expr{{|x| [[:confkey], [x]), x]}, 32, rtable[:attnum])}
574   end
576   ds = metadata_dataset.
577     from{}.
578     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
579     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>, Sequel[:co][:conkey])).
580     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
581     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>, Sequel[:co][:confkey])).
582     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
583     order{[co[:conname], cpos]}.
584     where{{
585       cl[:relkind]=>'r',
586       co[:contype]=>'f',
587       cl[:oid]=>oid,
588       cpos=>rpos
589     }}.
590     select{[
591       co[:conname].as(:name),
592       ctable[:attname].as(:column),
593       co[:confupdtype].as(:on_update),
594       co[:confdeltype].as(:on_delete),
595       cl2[:relname].as(:table),
596       rtable[:attname].as(:refcolumn),
597, co[:condeferrable], co[:condeferred]).as(:deferrable),
598       nsp[:nspname].as(:schema)
599     ]}
601   if reverse
602     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
603   end
605   h = {}
608   ds.each do |row|
609     if reverse
610       key = [row[:schema], row[:table], row[:name]]
611     else
612       key = row[:name]
613     end
615     if r = h[key]
616       r[:columns] <<[:column])
617       r[:key] <<[:refcolumn])
618     else
619       entry = h[key] = {
620         :name=>[:name]),
621         :columns=>[[:column])],
622         :key=>[[:refcolumn])],
623         :on_update=>fklod_map[row[:on_update]],
624         :on_delete=>fklod_map[row[:on_delete]],
625         :deferrable=>row[:deferrable],
626         :table=>schema ?[:schema]),[:table])) :[:table]),
627       }
629       unless schema
630         # If not combining schema information into the :table entry
631         # include it as a separate entry.
632         entry[:schema] =[:schema])
633       end
634     end
635   end
637   h.values
638 end
[show source]
    # File lib/sequel/adapters/shared/postgres.rb
640 def freeze
641   server_version
642   supports_prepared_transactions?
643   @conversion_procs.freeze
644   super
645 end
indexes(table, opts=OPTS)

Use the pg_* system tables to determine indexes on a table

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
648 def indexes(table, opts=OPTS)
649   m = output_identifier_meth
650   oid = regclass_oid(table, opts)
652   if server_version >= 90500
653     order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
654   else
655     range = 0...32
656     order = [Sequel[:indc][:relname],{|x| [[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
657   end
659   attnums =, Sequel[:ind][:indkey])
661   ds = metadata_dataset.
662     from{}.
663     join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
664     join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
665     join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
666     left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
667     where{{
668       indc[:relkind]=>'i',
669       ind[:indisprimary]=>false,
670       :indexprs=>nil,
671       :indisvalid=>true,
672       tab[:oid]=>oid}}.
673     order(*order).
674     select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}
676   ds = ds.where(:indpred=>nil) unless opts[:include_partial]
677   ds = ds.where(:indisready=>true) if server_version >= 80300
678   ds = ds.where(:indislive=>true) if server_version >= 90300
680   indexes = {}
681   ds.each do |r|
682     i = indexes[[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
683     i[:columns] <<[:column])
684   end
685   indexes
686 end

Dataset containing all current database locks

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
689 def locks
690   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname],]}
691 end
notify(channel, opts=OPTS)

Notifies the given channel. See the PostgreSQL NOTIFY documentation. Options:


The payload string to use for the NOTIFY statement. Only supported in PostgreSQL 9.0+.


The server to which to send the NOTIFY statement, if the sharding support is being used.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
699 def notify(channel, opts=OPTS)
700   sql =
701   sql << "NOTIFY "
702   dataset.send(:identifier_append, sql, channel)
703   if payload = opts[:payload]
704     sql << ", "
705     dataset.literal_append(sql, payload.to_s)
706   end
707   execute_ddl(sql, opts)
708 end
primary_key(table, opts=OPTS)

Return primary key for the given table.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
711 def primary_key(table, opts=OPTS)
712   quoted_table = quote_schema_table(table)
713   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
714   sql = "#{SELECT_PK_SQL} AND pg_class.oid = #{literal(regclass_oid(table, opts))}"
715   value = fetch(sql).single_value
716   Sequel.synchronize{@primary_keys[quoted_table] = value}
717 end
primary_key_sequence(table, opts=OPTS)

Return the sequence providing the default for the primary key for the given table.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
720 def primary_key_sequence(table, opts=OPTS)
721   quoted_table = quote_schema_table(table)
722   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
723   sql = "#{SELECT_SERIAL_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
724   if pks = fetch(sql).single_record
725     value = literal([:schema], pks[:sequence]))
726     Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
727   else
728     sql = "#{SELECT_CUSTOM_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}"
729     if pks = fetch(sql).single_record
730       value = literal([:schema],[:sequence])))
731       Sequel.synchronize{@primary_key_sequences[quoted_table] = value}
732     end
733   end
734 end
refresh_view(name, opts=OPTS)

Refresh the materialized view with the given name.

DB.refresh_view(:items_view, :concurrently=>true)
[show source]
    # File lib/sequel/adapters/shared/postgres.rb
742 def refresh_view(name, opts=OPTS)
743   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}"
744 end

Reset the primary key sequence for the given table, basing it on the maximum current value of the table's primary key.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
748 def reset_primary_key_sequence(table)
749   return unless seq = primary_key_sequence(table)
750   pk =
751   db = self
752   s, t = schema_and_table(table)
753   table = Sequel.qualify(s, t) if s
755   if server_version >= 100000
756     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(
757     increment_by = :seqincrement
758     min_value = :seqmin
759   else
760     seq_ds = metadata_dataset.from(
761     increment_by = :increment_by
762     min_value = :min_value
763   end
765   get{setval(seq, db[table].select(coalesce(max(pk),, false)}
766 end
rollback_prepared_transaction(transaction_id, opts=OPTS)
[show source]
    # File lib/sequel/adapters/shared/postgres.rb
768 def rollback_prepared_transaction(transaction_id, opts=OPTS)
769   run("ROLLBACK PREPARED #{literal(transaction_id)}", opts)
770 end

PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
774 def serial_primary_key_options
775   auto_increment_key = server_version >= 100002 ? :identity : :serial
776   {:primary_key => true, auto_increment_key => true, :type=>Integer}
777 end

The version of the PostgreSQL server, used for determining capability.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
780 def server_version(server=nil)
781   return @server_version if @server_version
782   ds = dataset
783   ds = ds.server(server) if server
784   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
785 end

PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
788 def supports_create_table_if_not_exists?
789   server_version >= 90100
790 end

PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
793 def supports_deferrable_constraints?
794   server_version >= 90000
795 end

PostgreSQL supports deferrable foreign key constraints.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
798 def supports_deferrable_foreign_key_constraints?
799   true
800 end


[show source]
    # File lib/sequel/adapters/shared/postgres.rb
803 def supports_drop_table_if_exists?
804   true
805 end

PostgreSQL supports partial indexes.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
808 def supports_partial_indexes?
809   true
810 end

PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
819 def supports_prepared_transactions?
820   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
821   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
822 end

PostgreSQL supports savepoints

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
825 def supports_savepoints?
826   true
827 end

PostgreSQL supports transaction isolation levels

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
830 def supports_transaction_isolation_levels?
831   true
832 end

PostgreSQL supports transaction DDL statements.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
835 def supports_transactional_ddl?
836   true
837 end

PostgreSQL 9.0+ supports trigger conditions.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
813 def supports_trigger_conditions?
814   server_version >= 90000
815 end
tables(opts=OPTS, &block)

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.



Return the tables as Sequel::SQL::QualifiedIdentifier instances, using the schema the table is located in as the qualifier.


The schema to search


The server to use

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
848 def tables(opts=OPTS, &block)
849   pg_class_relname(['r', 'p'], opts, &block)
850 end

Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
854 def type_supported?(type)
855   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
856   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
857   Sequel.synchronize{return @supported_types[type] = supported}
858 end

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
[show source]
    # File lib/sequel/adapters/shared/postgres.rb
867 def values(v)
868   @default_dataset.clone(:values=>v)
869 end

Array of symbols specifying view names in the current database.



Return materialized views


Return the views as Sequel::SQL::QualifiedIdentifier instances, using the schema the view is located in as the qualifier.


The schema to search


The server to use

[show source]
    # File lib/sequel/adapters/shared/postgres.rb
879 def views(opts=OPTS)
880   relkind = opts[:materialized] ? 'm' : 'v'
881   pg_class_relname(relkind, opts)
882 end