# File lib/sequel/adapters/shared/postgres.rb 688 def primary_key(table, opts=OPTS) 689 quoted_table = quote_schema_table(table) 690 Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)} 691 value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts)) 692 Sequel.synchronize{@primary_keys[quoted_table] = value} 693 end
module Sequel::Postgres::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- FOREIGN_KEY_LIST_ON_DELETE_MAP
- MAX_DATE
- MAX_TIMESTAMP
- MIN_DATE
- MIN_TIMESTAMP
- ON_COMMIT
- 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.- TYPTYPE_METHOD_MAP
- VALID_CLIENT_MIN_MESSAGES
Attributes
A hash of conversion procs, keyed by type integer (oid) and having callable values for the conversion proc for that type.
Public Instance Methods
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 309 def add_conversion_proc(oid, callable=nil, &block) 310 conversion_procs[oid] = callable || block 311 end
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 316 def add_named_conversion_proc(name, &block) 317 unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid) 318 raise Error, "No matching type in pg_type for #{name.inspect}" 319 end 320 add_conversion_proc(oid, block) 321 end
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 332 def check_constraints(table) 333 m = output_identifier_meth 334 335 hash = {} 336 _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row| 337 constraint = m.call(row[:constraint]) 338 entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]} 339 entry[:columns] << m.call(row[:column]) if row[:column] 340 end 341 342 hash 343 end
# File lib/sequel/adapters/shared/postgres.rb 323 def commit_prepared_transaction(transaction_id, opts=OPTS) 324 run("COMMIT PREPARED #{literal(transaction_id)}", opts) 325 end
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 363 def convert_serial_to_identity(table, opts=OPTS) 364 raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002 365 366 server = opts[:server] 367 server_hash = server ? {:server=>server} : OPTS 368 ds = dataset 369 ds = ds.server(server) if server 370 371 raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on' 372 373 table_oid = regclass_oid(table) 374 im = input_identifier_meth 375 unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0])) 376 raise Error, "could not determine column to convert from serial to identity automatically" 377 end 378 column = im.call(column) 379 380 column_num = ds.from(:pg_attribute). 381 where(:attrelid=>table_oid, :attname=>column). 382 get(:attnum) 383 384 pg_class = Sequel.cast('pg_class', :regclass) 385 res = ds.from(:pg_depend). 386 where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i'). 387 select_map([:objid, Sequel.as({:deptype=>'i'}, :v)]) 388 389 case res.length 390 when 0 391 raise Error, "unable to find related sequence when converting serial to identity" 392 when 1 393 seq_oid, already_identity = res.first 394 else 395 raise Error, "more than one linked sequence found when converting serial to identity" 396 end 397 398 return if already_identity 399 400 transaction(server_hash) do 401 run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash) 402 403 ds.from(:pg_depend). 404 where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a'). 405 update(:deptype=>'i') 406 407 ds.from(:pg_attribute). 408 where(:attrelid=>table_oid, :attname=>column). 409 update(:attidentity=>'d') 410 end 411 412 remove_cached_schema(table) 413 nil 414 end
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.
- :parallel
-
The thread safety attribute of the function. Should be SAFE, UNSAFE, RESTRICTED. PostgreSQL assumes UNSAFE 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 437 def create_function(name, definition, opts=OPTS) 438 self << create_function_sql(name, definition, opts) 439 end
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 448 def create_language(name, opts=OPTS) 449 self << create_language_sql(name, opts) 450 end
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 457 def create_schema(name, opts=OPTS) 458 self << create_schema_sql(name, opts) 459 end
Support partitions of tables using the :partition_of option.
# File lib/sequel/adapters/shared/postgres.rb 462 def create_table(name, options=OPTS, &block) 463 if options[:partition_of] 464 create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options) 465 return 466 end 467 468 super 469 end
Support partitions of tables using the :partition_of option.
# File lib/sequel/adapters/shared/postgres.rb 472 def create_table?(name, options=OPTS, &block) 473 if options[:partition_of] 474 create_table(name, options.merge!(:if_not_exists=>true), &block) 475 return 476 end 477 478 super 479 end
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.
- :replace
-
Replace the trigger with the same name if it already exists (PostgreSQL 14+).
- :when
-
A filter to use for the trigger
# File lib/sequel/adapters/shared/postgres.rb 493 def create_trigger(table, name, function, opts=OPTS) 494 self << create_trigger_sql(table, name, function, opts) 495 end
# File lib/sequel/adapters/shared/postgres.rb 497 def database_type 498 :postgres 499 end
For constraints that are deferrable, defer constraints until transaction commit. Options:
- :constraints
-
An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.
- :server
-
The server/shard on which to run the query.
Examples:
DB.defer_constraints # SET CONSTRAINTS ALL DEFERRED DB.defer_constraints(constraints: [:c1, Sequel[:sc][:c2]]) # SET CONSTRAINTS "c1", "sc"."s2" DEFERRED
# File lib/sequel/adapters/shared/postgres.rb 516 def defer_constraints(opts=OPTS) 517 _set_constraints(' DEFERRED', opts) 518 end
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 525 def do(code, opts=OPTS) 526 language = opts[:language] 527 run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}" 528 end
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 536 def drop_function(name, opts=OPTS) 537 self << drop_function_sql(name, opts) 538 end
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 545 def drop_language(name, opts=OPTS) 546 self << drop_language_sql(name, opts) 547 end
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 554 def drop_schema(name, opts=OPTS) 555 self << drop_schema_sql(name, opts) 556 end
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 564 def drop_trigger(table, name, opts=OPTS) 565 self << drop_trigger_sql(table, name, opts) 566 end
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 578 def foreign_key_list(table, opts=OPTS) 579 m = output_identifier_meth 580 schema, _ = opts.fetch(:schema, schema_and_table(table)) 581 582 h = {} 583 fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 584 reverse = opts[:reverse] 585 586 (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row| 587 if reverse 588 key = [row[:schema], row[:table], row[:name]] 589 else 590 key = row[:name] 591 end 592 593 if r = h[key] 594 r[:columns] << m.call(row[:column]) 595 r[:key] << m.call(row[:refcolumn]) 596 else 597 entry = h[key] = { 598 :name=>m.call(row[:name]), 599 :columns=>[m.call(row[:column])], 600 :key=>[m.call(row[:refcolumn])], 601 :on_update=>fklod_map[row[:on_update]], 602 :on_delete=>fklod_map[row[:on_delete]], 603 :deferrable=>row[:deferrable], 604 :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]), 605 } 606 607 unless schema 608 # If not combining schema information into the :table entry 609 # include it as a separate entry. 610 entry[:schema] = m.call(row[:schema]) 611 end 612 end 613 end 614 615 h.values 616 end
# File lib/sequel/adapters/shared/postgres.rb 618 def freeze 619 server_version 620 supports_prepared_transactions? 621 _schema_ds 622 _select_serial_sequence_ds 623 _select_custom_sequence_ds 624 _select_pk_ds 625 _indexes_ds 626 _check_constraints_ds 627 _foreign_key_list_ds 628 _reverse_foreign_key_list_ds 629 @conversion_procs.freeze 630 super 631 end
Immediately apply deferrable constraints.
- :constraints
-
An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.
- :server
-
The server/shard on which to run the query.
Examples:
DB.immediate_constraints # SET CONSTRAINTS ALL IMMEDIATE DB.immediate_constraints(constraints: [:c1, Sequel[:sc][:c2]]) # SET CONSTRAINTS "c1", "sc"."s2" IMMEDIATE
# File lib/sequel/adapters/shared/postgres.rb 647 def immediate_constraints(opts=OPTS) 648 _set_constraints(' IMMEDIATE', opts) 649 end
Use the pg_* system tables to determine indexes on a table
# File lib/sequel/adapters/shared/postgres.rb 652 def indexes(table, opts=OPTS) 653 m = output_identifier_meth 654 cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)} 655 cond[:indpred] = nil unless opts[:include_partial] 656 657 indexes = {} 658 _indexes_ds.where_each(cond) do |r| 659 i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]} 660 i[:columns] << m.call(r[:column]) 661 end 662 indexes 663 end
Dataset
containing all current database locks
# File lib/sequel/adapters/shared/postgres.rb 666 def locks 667 dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]} 668 end
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 676 def notify(channel, opts=OPTS) 677 sql = String.new 678 sql << "NOTIFY " 679 dataset.send(:identifier_append, sql, channel) 680 if payload = opts[:payload] 681 sql << ", " 682 dataset.literal_append(sql, payload.to_s) 683 end 684 execute_ddl(sql, opts) 685 end
Return primary key for the given table.
Return the sequence providing the default for the primary key for the given table.
# File lib/sequel/adapters/shared/postgres.rb 696 def primary_key_sequence(table, opts=OPTS) 697 quoted_table = quote_schema_table(table) 698 Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)} 699 cond = {Sequel[:t][:oid] => regclass_oid(table, opts)} 700 value = if pks = _select_serial_sequence_ds.first(cond) 701 literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence])) 702 elsif pks = _select_custom_sequence_ds.first(cond) 703 literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence]))) 704 end 705 706 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value 707 end
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 715 def refresh_view(name, opts=OPTS) 716 run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}" 717 end
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 721 def reset_primary_key_sequence(table) 722 return unless seq = primary_key_sequence(table) 723 pk = SQL::Identifier.new(primary_key(table)) 724 db = self 725 s, t = schema_and_table(table) 726 table = Sequel.qualify(s, t) if s 727 728 if server_version >= 100000 729 seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq))) 730 increment_by = :seqincrement 731 min_value = :seqmin 732 # :nocov: 733 else 734 seq_ds = metadata_dataset.from(LiteralString.new(seq)) 735 increment_by = :increment_by 736 min_value = :min_value 737 # :nocov: 738 end 739 740 get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)} 741 end
# File lib/sequel/adapters/shared/postgres.rb 743 def rollback_prepared_transaction(transaction_id, opts=OPTS) 744 run("ROLLBACK PREPARED #{literal(transaction_id)}", opts) 745 end
PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.
# File lib/sequel/adapters/shared/postgres.rb 749 def serial_primary_key_options 750 # :nocov: 751 auto_increment_key = server_version >= 100002 ? :identity : :serial 752 # :nocov: 753 {:primary_key => true, auto_increment_key => true, :type=>Integer} 754 end
The version of the PostgreSQL server, used for determining capability.
# File lib/sequel/adapters/shared/postgres.rb 757 def server_version(server=nil) 758 return @server_version if @server_version 759 ds = dataset 760 ds = ds.server(server) if server 761 @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0 762 end
PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+
# File lib/sequel/adapters/shared/postgres.rb 765 def supports_create_table_if_not_exists? 766 server_version >= 90100 767 end
PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 770 def supports_deferrable_constraints? 771 server_version >= 90000 772 end
PostgreSQL supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 775 def supports_deferrable_foreign_key_constraints? 776 true 777 end
PostgreSQL supports DROP TABLE IF EXISTS
# File lib/sequel/adapters/shared/postgres.rb 780 def supports_drop_table_if_exists? 781 true 782 end
PostgreSQL supports partial indexes.
# File lib/sequel/adapters/shared/postgres.rb 785 def supports_partial_indexes? 786 true 787 end
PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.
# File lib/sequel/adapters/shared/postgres.rb 796 def supports_prepared_transactions? 797 return @supports_prepared_transactions if defined?(@supports_prepared_transactions) 798 @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0 799 end
PostgreSQL supports savepoints
# File lib/sequel/adapters/shared/postgres.rb 802 def supports_savepoints? 803 true 804 end
PostgreSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/postgres.rb 807 def supports_transaction_isolation_levels? 808 true 809 end
PostgreSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/postgres.rb 812 def supports_transactional_ddl? 813 true 814 end
PostgreSQL 9.0+ supports trigger conditions.
# File lib/sequel/adapters/shared/postgres.rb 790 def supports_trigger_conditions? 791 server_version >= 90000 792 end
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 825 def tables(opts=OPTS, &block) 826 pg_class_relname(['r', 'p'], opts, &block) 827 end
Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.
# File lib/sequel/adapters/shared/postgres.rb 831 def type_supported?(type) 832 Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)} 833 supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0 834 Sequel.synchronize{return @supported_types[type] = supported} 835 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
# File lib/sequel/adapters/shared/postgres.rb 844 def values(v) 845 raise Error, "Cannot provide an empty array for values" if v.empty? 846 @default_dataset.clone(:values=>v) 847 end
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 857 def views(opts=OPTS) 858 relkind = opts[:materialized] ? 'm' : 'v' 859 pg_class_relname(relkind, opts) 860 end
Private Instance Methods
Build dataset used for foreign key list methods.
# File lib/sequel/adapters/shared/postgres.rb 884 def __foreign_key_list_ds(reverse) 885 if reverse 886 ctable = Sequel[:att2] 887 cclass = Sequel[:cl2] 888 rtable = Sequel[:att] 889 rclass = Sequel[:cl] 890 else 891 ctable = Sequel[:att] 892 cclass = Sequel[:cl] 893 rtable = Sequel[:att2] 894 rclass = Sequel[:cl2] 895 end 896 897 if server_version >= 90500 898 cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])} 899 rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])} 900 # :nocov: 901 else 902 range = 0...32 903 cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])} 904 rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])} 905 # :nocov: 906 end 907 908 ds = metadata_dataset. 909 from{pg_constraint.as(:co)}. 910 join(Sequel[:pg_class].as(cclass), :oid=>:conrelid). 911 join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 912 join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]). 913 join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])). 914 join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]). 915 order{[co[:conname], cpos]}. 916 where{{ 917 cl[:relkind]=>%w'r p', 918 co[:contype]=>'f', 919 cpos=>rpos 920 }}. 921 select{[ 922 co[:conname].as(:name), 923 ctable[:attname].as(:column), 924 co[:confupdtype].as(:on_update), 925 co[:confdeltype].as(:on_delete), 926 cl2[:relname].as(:table), 927 rtable[:attname].as(:refcolumn), 928 SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable), 929 nsp[:nspname].as(:schema) 930 ]} 931 932 if reverse 933 ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname]) 934 end 935 936 ds 937 end
Dataset
used to retrieve CHECK constraint information
# File lib/sequel/adapters/shared/postgres.rb 865 def _check_constraints_ds 866 @_check_constraints_ds ||= metadata_dataset. 867 from{pg_constraint.as(:co)}. 868 left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 869 where(:contype=>'c'). 870 select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]} 871 end
Dataset
used to retrieve foreign keys referenced by a table
# File lib/sequel/adapters/shared/postgres.rb 874 def _foreign_key_list_ds 875 @_foreign_key_list_ds ||= __foreign_key_list_ds(false) 876 end
Dataset
used to retrieve index information
# File lib/sequel/adapters/shared/postgres.rb 940 def _indexes_ds 941 @_indexes_ds ||= begin 942 if server_version >= 90500 943 order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])] 944 # :nocov: 945 else 946 range = 0...32 947 order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])] 948 # :nocov: 949 end 950 951 attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey]) 952 953 ds = metadata_dataset. 954 from{pg_class.as(:tab)}. 955 join(Sequel[:pg_index].as(:ind), :indrelid=>:oid). 956 join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid). 957 join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums). 958 left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]). 959 where{{ 960 indc[:relkind]=>%w'i I', 961 ind[:indisprimary]=>false, 962 :indexprs=>nil, 963 :indisvalid=>true}}. 964 order(*order). 965 select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]} 966 967 # :nocov: 968 ds = ds.where(:indisready=>true) if server_version >= 80300 969 ds = ds.where(:indislive=>true) if server_version >= 90300 970 # :nocov: 971 972 ds 973 end 974 end
Dataset
used to retrieve foreign keys referencing a table
# File lib/sequel/adapters/shared/postgres.rb 879 def _reverse_foreign_key_list_ds 880 @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true) 881 end
Dataset
used to get schema for tables
# File lib/sequel/adapters/shared/postgres.rb 1037 def _schema_ds 1038 @_schema_ds ||= begin 1039 ds = metadata_dataset.select{[ 1040 pg_attribute[:attname].as(:name), 1041 SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid), 1042 SQL::Cast.new(basetype[:oid], :integer).as(:base_oid), 1043 SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type), 1044 SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type), 1045 SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default), 1046 SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null), 1047 SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key), 1048 Sequel[:pg_type][:typtype], 1049 (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array), 1050 ]}. 1051 from(:pg_class). 1052 join(:pg_attribute, :attrelid=>:oid). 1053 join(:pg_type, :oid=>:atttypid). 1054 left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype). 1055 left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]). 1056 left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]). 1057 left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true). 1058 where{{pg_attribute[:attisdropped]=>false}}. 1059 where{pg_attribute[:attnum] > 0}. 1060 order{pg_attribute[:attnum]} 1061 1062 # :nocov: 1063 if server_version > 100000 1064 # :nocov: 1065 ds = ds.select_append{pg_attribute[:attidentity]} 1066 1067 # :nocov: 1068 if server_version > 120000 1069 # :nocov: 1070 ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)} 1071 end 1072 end 1073 1074 ds 1075 end 1076 end
Dataset
used to determine custom serial sequences for tables
# File lib/sequel/adapters/shared/postgres.rb 977 def _select_custom_sequence_ds 978 @_select_custom_sequence_ds ||= metadata_dataset. 979 from{pg_class.as(:t)}. 980 join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name). 981 join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr). 982 join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def). 983 join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons). 984 where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}. 985 select{ 986 expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2) 987 [ 988 name[:nspname].as(:schema), 989 Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence) 990 ] 991 } 992 end
Dataset
used to determine primary keys for tables
# File lib/sequel/adapters/shared/postgres.rb 1023 def _select_pk_ds 1024 @_select_pk_ds ||= metadata_dataset. 1025 from(:pg_class, :pg_attribute, :pg_index, :pg_namespace). 1026 where{[ 1027 [pg_class[:oid], pg_attribute[:attrelid]], 1028 [pg_class[:relnamespace], pg_namespace[:oid]], 1029 [pg_class[:oid], pg_index[:indrelid]], 1030 [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]], 1031 [pg_index[:indisprimary], 't'] 1032 ]}. 1033 select{pg_attribute[:attname].as(:pk)} 1034 end
Dataset
used to determine normal serial sequences for tables
# File lib/sequel/adapters/shared/postgres.rb 995 def _select_serial_sequence_ds 996 @_serial_sequence_ds ||= metadata_dataset. 997 from{[ 998 pg_class.as(:seq), 999 pg_attribute.as(:attr), 1000 pg_depend.as(:dep), 1001 pg_namespace.as(:name), 1002 pg_constraint.as(:cons), 1003 pg_class.as(:t) 1004 ]}. 1005 where{[ 1006 [seq[:oid], dep[:objid]], 1007 [seq[:relnamespace], name[:oid]], 1008 [seq[:relkind], 'S'], 1009 [attr[:attrelid], dep[:refobjid]], 1010 [attr[:attnum], dep[:refobjsubid]], 1011 [attr[:attrelid], cons[:conrelid]], 1012 [attr[:attnum], cons[:conkey].sql_subscript(1)], 1013 [attr[:attrelid], t[:oid]], 1014 [cons[:contype], 'p'] 1015 ]}. 1016 select{[ 1017 name[:nspname].as(:schema), 1018 seq[:relname].as(:sequence) 1019 ]} 1020 end
Internals of defer_constraints/immediate_constraints
# File lib/sequel/adapters/shared/postgres.rb 1079 def _set_constraints(type, opts) 1080 execute_ddl(_set_constraints_sql(type, opts), opts) 1081 end
SQL
to use for SET CONSTRAINTS
# File lib/sequel/adapters/shared/postgres.rb 1084 def _set_constraints_sql(type, opts) 1085 sql = String.new 1086 sql << "SET CONSTRAINTS " 1087 if constraints = opts[:constraints] 1088 dataset.send(:source_list_append, sql, Array(constraints)) 1089 else 1090 sql << "ALL" 1091 end 1092 sql << type 1093 end
Consider lock or statement timeout errors as evidence that the table exists but is locked.
# File lib/sequel/adapters/shared/postgres.rb 1097 def _table_exists?(ds) 1098 super 1099 rescue DatabaseError => e 1100 raise e unless /canceling statement due to (?:statement|lock) timeout/ =~ e.message 1101 end
# File lib/sequel/adapters/shared/postgres.rb 1103 def alter_table_add_column_sql(table, op) 1104 "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}" 1105 end
# File lib/sequel/adapters/shared/postgres.rb 1121 def alter_table_drop_column_sql(table, op) 1122 "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}" 1123 end
# File lib/sequel/adapters/shared/postgres.rb 1107 def alter_table_generator_class 1108 Postgres::AlterTableGenerator 1109 end
# File lib/sequel/adapters/shared/postgres.rb 1111 def alter_table_set_column_type_sql(table, op) 1112 s = super 1113 if using = op[:using] 1114 using = Sequel::LiteralString.new(using) if using.is_a?(String) 1115 s += ' USING ' 1116 s << literal(using) 1117 end 1118 s 1119 end
# File lib/sequel/adapters/shared/postgres.rb 1125 def alter_table_validate_constraint_sql(table, op) 1126 "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}" 1127 end
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.
# File lib/sequel/adapters/shared/postgres.rb 1132 def begin_new_transaction(conn, opts) 1133 super 1134 if opts.has_key?(:synchronous) 1135 case sync = opts[:synchronous] 1136 when true 1137 sync = :on 1138 when false 1139 sync = :off 1140 when nil 1141 return 1142 end 1143 1144 log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}") 1145 end 1146 end
Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.
# File lib/sequel/adapters/shared/postgres.rb 1149 def begin_savepoint(conn, opts) 1150 super 1151 1152 unless (read_only = opts[:read_only]).nil? 1153 log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}") 1154 end 1155 end
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 1159 def column_definition_collate_sql(sql, column) 1160 if collate = column[:collate] 1161 collate = literal(collate) unless collate.is_a?(String) 1162 sql << " COLLATE #{collate}" 1163 end 1164 end
Support identity columns, but only use the identity SQL
syntax if no default value is given.
# File lib/sequel/adapters/shared/postgres.rb 1168 def column_definition_default_sql(sql, column) 1169 super 1170 if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default] 1171 if (identity = column[:identity]) 1172 sql << " GENERATED " 1173 sql << (identity == :always ? "ALWAYS" : "BY DEFAULT") 1174 sql << " AS IDENTITY" 1175 elsif (generated = column[:generated_always_as]) 1176 sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED" 1177 end 1178 end 1179 end
Handle PostgreSQL specific default format.
# File lib/sequel/adapters/shared/postgres.rb 1182 def column_schema_normalize_default(default, type) 1183 if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default) 1184 default = m[1] || m[2] 1185 end 1186 super(default, type) 1187 end
PostgreSQL can't combine rename_column operations, and it can combine the custom validate_constraint operation.
# File lib/sequel/adapters/shared/postgres.rb 1201 def combinable_alter_table_op?(op) 1202 (super || op[:op] == :validate_constraint) && op[:op] != :rename_column 1203 end
If the :prepare option is given and we aren't in a savepoint, prepare the transaction for a two-phase commit.
# File lib/sequel/adapters/shared/postgres.rb 1191 def commit_transaction(conn, opts=OPTS) 1192 if (s = opts[:prepare]) && savepoint_level(conn) <= 1 1193 log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}") 1194 else 1195 super 1196 end 1197 end
The SQL
queries to execute when starting a new connection.
# File lib/sequel/adapters/shared/postgres.rb 1207 def connection_configuration_sqls(opts=@opts) 1208 sqls = [] 1209 1210 sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true)) 1211 1212 cmm = opts.fetch(:client_min_messages, :warning) 1213 if cmm && !cmm.to_s.empty? 1214 cmm = cmm.to_s.upcase.strip 1215 unless VALID_CLIENT_MIN_MESSAGES.include?(cmm) 1216 raise Error, "Unsupported client_min_messages setting: #{cmm}" 1217 end 1218 sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'" 1219 end 1220 1221 if search_path = opts[:search_path] 1222 case search_path 1223 when String 1224 search_path = search_path.split(",").map(&:strip) 1225 when Array 1226 # nil 1227 else 1228 raise Error, "unrecognized value for :search_path option: #{search_path.inspect}" 1229 end 1230 sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}" 1231 end 1232 1233 sqls 1234 end
Handle exclusion constraints.
# File lib/sequel/adapters/shared/postgres.rb 1237 def constraint_definition_sql(constraint) 1238 case constraint[:type] 1239 when :exclude 1240 elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ') 1241 sql = String.new 1242 sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}" 1243 constraint_deferrable_sql_append(sql, constraint[:deferrable]) 1244 sql 1245 when :foreign_key, :check 1246 sql = super 1247 if constraint[:not_valid] 1248 sql << " NOT VALID" 1249 end 1250 sql 1251 else 1252 super 1253 end 1254 end
SQL
for doing fast table insert from stdin.
# File lib/sequel/adapters/shared/postgres.rb 1285 def copy_into_sql(table, opts) 1286 sql = String.new 1287 sql << "COPY #{literal(table)}" 1288 if cols = opts[:columns] 1289 sql << literal(Array(cols)) 1290 end 1291 sql << " FROM STDIN" 1292 if opts[:options] || opts[:format] 1293 sql << " (" 1294 sql << "FORMAT #{opts[:format]}" if opts[:format] 1295 sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1296 sql << ')' 1297 end 1298 sql 1299 end
SQL
for doing fast table output to stdout.
# File lib/sequel/adapters/shared/postgres.rb 1302 def copy_table_sql(table, opts) 1303 if table.is_a?(String) 1304 table 1305 else 1306 if opts[:options] || opts[:format] 1307 options = String.new 1308 options << " (" 1309 options << "FORMAT #{opts[:format]}" if opts[:format] 1310 options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1311 options << ')' 1312 end 1313 table = if table.is_a?(::Sequel::Dataset) 1314 "(#{table.sql})" 1315 else 1316 literal(table) 1317 end 1318 "COPY #{table} TO STDOUT#{options}" 1319 end 1320 end
SQL
statement to create database function.
# File lib/sequel/adapters/shared/postgres.rb 1323 def create_function_sql(name, definition, opts=OPTS) 1324 args = opts[:args] 1325 if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)} 1326 returns = opts[:returns] || 'void' 1327 end 1328 language = opts[:language] || 'SQL' 1329 <<-END 1330 CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)} 1331 #{"RETURNS #{returns}" if returns} 1332 LANGUAGE #{language} 1333 #{opts[:behavior].to_s.upcase if opts[:behavior]} 1334 #{'STRICT' if opts[:strict]} 1335 #{'SECURITY DEFINER' if opts[:security_definer]} 1336 #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]} 1337 #{"COST #{opts[:cost]}" if opts[:cost]} 1338 #{"ROWS #{opts[:rows]}" if opts[:rows]} 1339 #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]} 1340 AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]} 1341 END 1342 end
SQL
for creating a procedural language.
# File lib/sequel/adapters/shared/postgres.rb 1345 def create_language_sql(name, opts=OPTS) 1346 "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]}" 1347 end
Create a partition of another table, used when the create_table
with the :partition_of option is given.
# File lib/sequel/adapters/shared/postgres.rb 1351 def create_partition_of_table_from_generator(name, generator, options) 1352 execute_ddl(create_partition_of_table_sql(name, generator, options)) 1353 end
SQL
for creating a partition of another table.
# File lib/sequel/adapters/shared/postgres.rb 1356 def create_partition_of_table_sql(name, generator, options) 1357 sql = create_table_prefix_sql(name, options).dup 1358 1359 sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}" 1360 1361 case generator.partition_type 1362 when :range 1363 from, to = generator.range 1364 sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}" 1365 when :list 1366 sql << " FOR VALUES IN #{literal(generator.list)}" 1367 when :hash 1368 mod, remainder = generator.hash_values 1369 sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})" 1370 else # when :default 1371 sql << " DEFAULT" 1372 end 1373 1374 sql << create_table_suffix_sql(name, options) 1375 1376 sql 1377 end
SQL
for creating a schema.
# File lib/sequel/adapters/shared/postgres.rb 1380 def create_schema_sql(name, opts=OPTS) 1381 "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}" 1382 end
# File lib/sequel/adapters/shared/postgres.rb 1438 def create_table_as_sql(name, sql, options) 1439 result = create_table_prefix_sql name, options 1440 if on_commit = options[:on_commit] 1441 result += " ON COMMIT #{ON_COMMIT[on_commit]}" 1442 end 1443 result += " AS #{sql}" 1444 end
# File lib/sequel/adapters/shared/postgres.rb 1446 def create_table_generator_class 1447 Postgres::CreateTableGenerator 1448 end
DDL statement for creating a table with the given name, columns, and options
# File lib/sequel/adapters/shared/postgres.rb 1385 def create_table_prefix_sql(name, options) 1386 prefix_sql = if options[:temp] 1387 raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged] 1388 raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign] 1389 temporary_table_sql 1390 elsif options[:foreign] 1391 raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged] 1392 'FOREIGN ' 1393 elsif options[:unlogged] 1394 'UNLOGGED ' 1395 end 1396 1397 "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}" 1398 end
SQL
for creating a table with PostgreSQL specific options
# File lib/sequel/adapters/shared/postgres.rb 1401 def create_table_sql(name, generator, options) 1402 "#{super}#{create_table_suffix_sql(name, options)}" 1403 end
Handle various PostgreSQl specific table extensions such as inheritance, partitioning, tablespaces, and foreign tables.
# File lib/sequel/adapters/shared/postgres.rb 1407 def create_table_suffix_sql(name, options) 1408 sql = String.new 1409 1410 if inherits = options[:inherits] 1411 sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})" 1412 end 1413 1414 if partition_by = options[:partition_by] 1415 sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}" 1416 end 1417 1418 if on_commit = options[:on_commit] 1419 raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp] 1420 raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit) 1421 sql << " ON COMMIT #{ON_COMMIT[on_commit]}" 1422 end 1423 1424 if tablespace = options[:tablespace] 1425 sql << " TABLESPACE #{quote_identifier(tablespace)}" 1426 end 1427 1428 if server = options[:foreign] 1429 sql << " SERVER #{quote_identifier(server)}" 1430 if foreign_opts = options[:options] 1431 sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})" 1432 end 1433 end 1434 1435 sql 1436 end
SQL
for creating a database trigger.
# File lib/sequel/adapters/shared/postgres.rb 1451 def create_trigger_sql(table, name, function, opts=OPTS) 1452 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 1453 whence = opts[:after] ? 'AFTER' : 'BEFORE' 1454 if filter = opts[:when] 1455 raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions? 1456 filter = " WHEN #{filter_expr(filter)}" 1457 end 1458 "CREATE #{'OR REPLACE ' if opts[:replace]}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(', ')})" 1459 end
DDL fragment for initial part of CREATE VIEW statement
# File lib/sequel/adapters/shared/postgres.rb 1462 def create_view_prefix_sql(name, options) 1463 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]) 1464 1465 if options[:security_invoker] 1466 sql += " WITH (security_invoker)" 1467 end 1468 1469 if tablespace = options[:tablespace] 1470 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1471 end 1472 1473 sql 1474 end
# File lib/sequel/adapters/shared/postgres.rb 1280 def database_error_regexps 1281 DATABASE_ERROR_REGEXPS 1282 end
# File lib/sequel/adapters/shared/postgres.rb 1256 def database_specific_error_class_from_sqlstate(sqlstate) 1257 if sqlstate == '23P01' 1258 ExclusionConstraintViolation 1259 elsif sqlstate == '40P01' 1260 SerializationFailure 1261 elsif sqlstate == '55P03' 1262 DatabaseLockTimeout 1263 else 1264 super 1265 end 1266 end
SQL
for dropping a function from the database.
# File lib/sequel/adapters/shared/postgres.rb 1477 def drop_function_sql(name, opts=OPTS) 1478 "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}" 1479 end
Support :if_exists, :cascade, and :concurrently options.
# File lib/sequel/adapters/shared/postgres.rb 1482 def drop_index_sql(table, op) 1483 sch, _ = schema_and_table(table) 1484 "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]}" 1485 end
SQL
for dropping a procedural language from the database.
# File lib/sequel/adapters/shared/postgres.rb 1488 def drop_language_sql(name, opts=OPTS) 1489 "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}" 1490 end
SQL
for dropping a schema from the database.
# File lib/sequel/adapters/shared/postgres.rb 1493 def drop_schema_sql(name, opts=OPTS) 1494 "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}" 1495 end
Support :foreign tables
# File lib/sequel/adapters/shared/postgres.rb 1503 def drop_table_sql(name, options) 1504 "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}" 1505 end
SQL
for dropping a trigger from the database.
# File lib/sequel/adapters/shared/postgres.rb 1498 def drop_trigger_sql(table, name, opts=OPTS) 1499 "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}" 1500 end
SQL
for dropping a view from the database.
# File lib/sequel/adapters/shared/postgres.rb 1508 def drop_view_sql(name, opts=OPTS) 1509 "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}" 1510 end
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 1514 def filter_schema(ds, opts) 1515 expr = if schema = opts[:schema] 1516 if schema.is_a?(SQL::Identifier) 1517 schema.value.to_s 1518 else 1519 schema.to_s 1520 end 1521 else 1522 Sequel.function(:any, Sequel.function(:current_schemas, false)) 1523 end 1524 ds.where{{pg_namespace[:nspname]=>expr}} 1525 end
# File lib/sequel/adapters/shared/postgres.rb 1527 def index_definition_sql(table_name, index) 1528 cols = index[:columns] 1529 index_name = index[:name] || default_index_name(table_name, cols) 1530 1531 expr = if o = index[:opclass] 1532 "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})" 1533 else 1534 literal(Array(cols)) 1535 end 1536 1537 if_not_exists = " IF NOT EXISTS" if index[:if_not_exists] 1538 unique = "UNIQUE " if index[:unique] 1539 index_type = index[:type] 1540 filter = index[:where] || index[:filter] 1541 filter = " WHERE #{filter_expr(filter)}" if filter 1542 nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil? 1543 1544 case index_type 1545 when :full_text 1546 expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))" 1547 index_type = index[:index_type] || :gin 1548 when :spatial 1549 index_type = :gist 1550 end 1551 1552 "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]}#{nulls_distinct}#{" TABLESPACE #{quote_identifier(index[:tablespace])}" if index[:tablespace]}#{filter}" 1553 end
Setup datastructures shared by all postgres adapters.
# File lib/sequel/adapters/shared/postgres.rb 1556 def initialize_postgres_adapter 1557 @primary_keys = {} 1558 @primary_key_sequences = {} 1559 @supported_types = {} 1560 procs = @conversion_procs = CONVERSION_PROCS.dup 1561 procs[1184] = procs[1114] = method(:to_application_timestamp) 1562 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/postgres.rb 1565 def pg_class_relname(type, opts) 1566 ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace) 1567 ds = filter_schema(ds, opts) 1568 m = output_identifier_meth 1569 if defined?(yield) 1570 yield(ds) 1571 elsif opts[:qualify] 1572 ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)} 1573 else 1574 ds.map{|r| m.call(r[:relname])} 1575 end 1576 end
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 1580 def regclass_oid(expr, opts=OPTS) 1581 if expr.is_a?(String) && !expr.is_a?(LiteralString) 1582 expr = Sequel.identifier(expr) 1583 end 1584 1585 sch, table = schema_and_table(expr) 1586 sch ||= opts[:schema] 1587 if sch 1588 expr = Sequel.qualify(sch, table) 1589 end 1590 1591 expr = if ds = opts[:dataset] 1592 ds.literal(expr) 1593 else 1594 literal(expr) 1595 end 1596 1597 Sequel.cast(expr.to_s,:regclass).cast(:oid) 1598 end
Remove the cached entries for primary keys and sequences when a table is changed.
# File lib/sequel/adapters/shared/postgres.rb 1601 def remove_cached_schema(table) 1602 tab = quote_schema_table(table) 1603 Sequel.synchronize do 1604 @primary_keys.delete(tab) 1605 @primary_key_sequences.delete(tab) 1606 end 1607 super 1608 end
SQL
DDL statement for renaming a table. PostgreSQL doesn't allow you to change a table's schema in a rename table operation, so specifying a new schema in new_name will not have an effect.
# File lib/sequel/adapters/shared/postgres.rb 1612 def rename_table_sql(name, new_name) 1613 "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}" 1614 end
The schema :type entry to use for array types.
# File lib/sequel/adapters/shared/postgres.rb 1629 def schema_array_type(db_type) 1630 :array 1631 end
Handle interval and citext types.
# File lib/sequel/adapters/shared/postgres.rb 1617 def schema_column_type(db_type) 1618 case db_type 1619 when /\Ainterval\z/io 1620 :interval 1621 when /\Acitext\z/io 1622 :string 1623 else 1624 super 1625 end 1626 end
The schema :type entry to use for row/composite types.
# File lib/sequel/adapters/shared/postgres.rb 1634 def schema_composite_type(db_type) 1635 :composite 1636 end
The schema :type entry to use for enum types.
# File lib/sequel/adapters/shared/postgres.rb 1639 def schema_enum_type(db_type) 1640 :enum 1641 end
The schema :type entry to use for multirange types.
# File lib/sequel/adapters/shared/postgres.rb 1649 def schema_multirange_type(db_type) 1650 :multirange 1651 end
The dataset used for parsing table schemas, using the pg_* system catalogs.
# File lib/sequel/adapters/shared/postgres.rb 1666 def schema_parse_table(table_name, opts) 1667 m = output_identifier_meth(opts[:dataset]) 1668 1669 _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row| 1670 row[:default] = nil if blank_object?(row[:default]) 1671 if row[:base_oid] 1672 row[:domain_oid] = row[:oid] 1673 row[:oid] = row.delete(:base_oid) 1674 row[:db_domain_type] = row[:db_type] 1675 row[:db_type] = row.delete(:db_base_type) 1676 else 1677 row.delete(:base_oid) 1678 row.delete(:db_base_type) 1679 end 1680 1681 db_type = row[:db_type] 1682 row[:type] = if row.delete(:is_array) 1683 schema_array_type(db_type) 1684 else 1685 send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type) 1686 end 1687 identity = row.delete(:attidentity) 1688 if row[:primary_key] 1689 row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd' 1690 end 1691 1692 # :nocov: 1693 if server_version >= 90600 1694 # :nocov: 1695 case row[:oid] 1696 when 1082 1697 row[:min_value] = MIN_DATE 1698 row[:max_value] = MAX_DATE 1699 when 1184, 1114 1700 if Sequel.datetime_class == Time 1701 row[:min_value] = MIN_TIMESTAMP 1702 row[:max_value] = MAX_TIMESTAMP 1703 end 1704 end 1705 end 1706 1707 [m.call(row.delete(:name)), row] 1708 end 1709 end
The schema :type entry to use for range types.
# File lib/sequel/adapters/shared/postgres.rb 1644 def schema_range_type(db_type) 1645 :range 1646 end
Set the transaction isolation level on the given connection
# File lib/sequel/adapters/shared/postgres.rb 1712 def set_transaction_isolation(conn, opts) 1713 level = opts.fetch(:isolation, transaction_isolation_level) 1714 read_only = opts[:read_only] 1715 deferrable = opts[:deferrable] 1716 if level || !read_only.nil? || !deferrable.nil? 1717 sql = String.new 1718 sql << "SET TRANSACTION" 1719 sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level 1720 sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil? 1721 sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil? 1722 log_connection_execute(conn, sql) 1723 end 1724 end
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 1727 def sql_function_args(args) 1728 "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})" 1729 end
PostgreSQL can combine multiple alter table ops into a single query.
# File lib/sequel/adapters/shared/postgres.rb 1732 def supports_combining_alter_table_ops? 1733 true 1734 end
PostgreSQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/postgres.rb 1737 def supports_create_or_replace_view? 1738 true 1739 end
Handle bigserial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1742 def type_literal_generic_bignum_symbol(column) 1743 column[:serial] ? :bigserial : super 1744 end
PostgreSQL uses the bytea data type for blobs
# File lib/sequel/adapters/shared/postgres.rb 1747 def type_literal_generic_file(column) 1748 :bytea 1749 end
Handle serial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1752 def type_literal_generic_integer(column) 1753 column[:serial] ? :serial : super 1754 end
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 1760 def type_literal_generic_string(column) 1761 if column[:text] 1762 :text 1763 elsif column[:fixed] 1764 "char(#{column[:size]||default_string_column_size})" 1765 elsif column[:text] == false || column[:size] 1766 "varchar(#{column[:size]||default_string_column_size})" 1767 else 1768 :text 1769 end 1770 end
PostgreSQL 9.4+ supports views with check option.
# File lib/sequel/adapters/shared/postgres.rb 1773 def view_with_check_option_support 1774 # :nocov: 1775 :local if server_version >= 90400 1776 # :nocov: 1777 end