# File lib/sequel/adapters/shared/postgres.rb 651 def primary_key(table, opts=OPTS) 652 quoted_table = quote_schema_table(table) 653 Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)} 654 value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts)) 655 Sequel.synchronize{@primary_keys[quoted_table] = value} 656 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
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 506 def do(code, opts=OPTS) 507 language = opts[:language] 508 run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}" 509 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 517 def drop_function(name, opts=OPTS) 518 self << drop_function_sql(name, opts) 519 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 526 def drop_language(name, opts=OPTS) 527 self << drop_language_sql(name, opts) 528 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 535 def drop_schema(name, opts=OPTS) 536 self << drop_schema_sql(name, opts) 537 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 545 def drop_trigger(table, name, opts=OPTS) 546 self << drop_trigger_sql(table, name, opts) 547 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 559 def foreign_key_list(table, opts=OPTS) 560 m = output_identifier_meth 561 schema, _ = opts.fetch(:schema, schema_and_table(table)) 562 563 h = {} 564 fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 565 reverse = opts[:reverse] 566 567 (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row| 568 if reverse 569 key = [row[:schema], row[:table], row[:name]] 570 else 571 key = row[:name] 572 end 573 574 if r = h[key] 575 r[:columns] << m.call(row[:column]) 576 r[:key] << m.call(row[:refcolumn]) 577 else 578 entry = h[key] = { 579 :name=>m.call(row[:name]), 580 :columns=>[m.call(row[:column])], 581 :key=>[m.call(row[:refcolumn])], 582 :on_update=>fklod_map[row[:on_update]], 583 :on_delete=>fklod_map[row[:on_delete]], 584 :deferrable=>row[:deferrable], 585 :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]), 586 } 587 588 unless schema 589 # If not combining schema information into the :table entry 590 # include it as a separate entry. 591 entry[:schema] = m.call(row[:schema]) 592 end 593 end 594 end 595 596 h.values 597 end
# File lib/sequel/adapters/shared/postgres.rb 599 def freeze 600 server_version 601 supports_prepared_transactions? 602 _schema_ds 603 _select_serial_sequence_ds 604 _select_custom_sequence_ds 605 _select_pk_ds 606 _indexes_ds 607 _check_constraints_ds 608 _foreign_key_list_ds 609 _reverse_foreign_key_list_ds 610 @conversion_procs.freeze 611 super 612 end
Use the pg_* system tables to determine indexes on a table
# File lib/sequel/adapters/shared/postgres.rb 615 def indexes(table, opts=OPTS) 616 m = output_identifier_meth 617 cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)} 618 cond[:indpred] = nil unless opts[:include_partial] 619 620 indexes = {} 621 _indexes_ds.where_each(cond) do |r| 622 i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]} 623 i[:columns] << m.call(r[:column]) 624 end 625 indexes 626 end
Dataset
containing all current database locks
# File lib/sequel/adapters/shared/postgres.rb 629 def locks 630 dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]} 631 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 639 def notify(channel, opts=OPTS) 640 sql = String.new 641 sql << "NOTIFY " 642 dataset.send(:identifier_append, sql, channel) 643 if payload = opts[:payload] 644 sql << ", " 645 dataset.literal_append(sql, payload.to_s) 646 end 647 execute_ddl(sql, opts) 648 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 659 def primary_key_sequence(table, opts=OPTS) 660 quoted_table = quote_schema_table(table) 661 Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)} 662 cond = {Sequel[:t][:oid] => regclass_oid(table, opts)} 663 value = if pks = _select_serial_sequence_ds.first(cond) 664 literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence])) 665 elsif pks = _select_custom_sequence_ds.first(cond) 666 literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence]))) 667 end 668 669 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value 670 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 678 def refresh_view(name, opts=OPTS) 679 run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}" 680 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 684 def reset_primary_key_sequence(table) 685 return unless seq = primary_key_sequence(table) 686 pk = SQL::Identifier.new(primary_key(table)) 687 db = self 688 s, t = schema_and_table(table) 689 table = Sequel.qualify(s, t) if s 690 691 if server_version >= 100000 692 seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq))) 693 increment_by = :seqincrement 694 min_value = :seqmin 695 # :nocov: 696 else 697 seq_ds = metadata_dataset.from(LiteralString.new(seq)) 698 increment_by = :increment_by 699 min_value = :min_value 700 # :nocov: 701 end 702 703 get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)} 704 end
# File lib/sequel/adapters/shared/postgres.rb 706 def rollback_prepared_transaction(transaction_id, opts=OPTS) 707 run("ROLLBACK PREPARED #{literal(transaction_id)}", opts) 708 end
PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.
# File lib/sequel/adapters/shared/postgres.rb 712 def serial_primary_key_options 713 # :nocov: 714 auto_increment_key = server_version >= 100002 ? :identity : :serial 715 # :nocov: 716 {:primary_key => true, auto_increment_key => true, :type=>Integer} 717 end
The version of the PostgreSQL server, used for determining capability.
# File lib/sequel/adapters/shared/postgres.rb 720 def server_version(server=nil) 721 return @server_version if @server_version 722 ds = dataset 723 ds = ds.server(server) if server 724 @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0 725 end
PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+
# File lib/sequel/adapters/shared/postgres.rb 728 def supports_create_table_if_not_exists? 729 server_version >= 90100 730 end
PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 733 def supports_deferrable_constraints? 734 server_version >= 90000 735 end
PostgreSQL supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 738 def supports_deferrable_foreign_key_constraints? 739 true 740 end
PostgreSQL supports DROP TABLE IF EXISTS
# File lib/sequel/adapters/shared/postgres.rb 743 def supports_drop_table_if_exists? 744 true 745 end
PostgreSQL supports partial indexes.
# File lib/sequel/adapters/shared/postgres.rb 748 def supports_partial_indexes? 749 true 750 end
PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.
# File lib/sequel/adapters/shared/postgres.rb 759 def supports_prepared_transactions? 760 return @supports_prepared_transactions if defined?(@supports_prepared_transactions) 761 @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0 762 end
PostgreSQL supports savepoints
# File lib/sequel/adapters/shared/postgres.rb 765 def supports_savepoints? 766 true 767 end
PostgreSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/postgres.rb 770 def supports_transaction_isolation_levels? 771 true 772 end
PostgreSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/postgres.rb 775 def supports_transactional_ddl? 776 true 777 end
PostgreSQL 9.0+ supports trigger conditions.
# File lib/sequel/adapters/shared/postgres.rb 753 def supports_trigger_conditions? 754 server_version >= 90000 755 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 788 def tables(opts=OPTS, &block) 789 pg_class_relname(['r', 'p'], opts, &block) 790 end
Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.
# File lib/sequel/adapters/shared/postgres.rb 794 def type_supported?(type) 795 Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)} 796 supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0 797 Sequel.synchronize{return @supported_types[type] = supported} 798 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 807 def values(v) 808 raise Error, "Cannot provide an empty array for values" if v.empty? 809 @default_dataset.clone(:values=>v) 810 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 820 def views(opts=OPTS) 821 relkind = opts[:materialized] ? 'm' : 'v' 822 pg_class_relname(relkind, opts) 823 end
Private Instance Methods
Build dataset used for foreign key list methods.
# File lib/sequel/adapters/shared/postgres.rb 847 def __foreign_key_list_ds(reverse) 848 if reverse 849 ctable = Sequel[:att2] 850 cclass = Sequel[:cl2] 851 rtable = Sequel[:att] 852 rclass = Sequel[:cl] 853 else 854 ctable = Sequel[:att] 855 cclass = Sequel[:cl] 856 rtable = Sequel[:att2] 857 rclass = Sequel[:cl2] 858 end 859 860 if server_version >= 90500 861 cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])} 862 rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])} 863 # :nocov: 864 else 865 range = 0...32 866 cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])} 867 rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])} 868 # :nocov: 869 end 870 871 ds = metadata_dataset. 872 from{pg_constraint.as(:co)}. 873 join(Sequel[:pg_class].as(cclass), :oid=>:conrelid). 874 join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 875 join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]). 876 join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])). 877 join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]). 878 order{[co[:conname], cpos]}. 879 where{{ 880 cl[:relkind]=>%w'r p', 881 co[:contype]=>'f', 882 cpos=>rpos 883 }}. 884 select{[ 885 co[:conname].as(:name), 886 ctable[:attname].as(:column), 887 co[:confupdtype].as(:on_update), 888 co[:confdeltype].as(:on_delete), 889 cl2[:relname].as(:table), 890 rtable[:attname].as(:refcolumn), 891 SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable), 892 nsp[:nspname].as(:schema) 893 ]} 894 895 if reverse 896 ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname]) 897 end 898 899 ds 900 end
Dataset
used to retrieve CHECK constraint information
# File lib/sequel/adapters/shared/postgres.rb 828 def _check_constraints_ds 829 @_check_constraints_ds ||= metadata_dataset. 830 from{pg_constraint.as(:co)}. 831 left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 832 where(:contype=>'c'). 833 select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]} 834 end
Dataset
used to retrieve foreign keys referenced by a table
# File lib/sequel/adapters/shared/postgres.rb 837 def _foreign_key_list_ds 838 @_foreign_key_list_ds ||= __foreign_key_list_ds(false) 839 end
Dataset
used to retrieve index information
# File lib/sequel/adapters/shared/postgres.rb 903 def _indexes_ds 904 @_indexes_ds ||= begin 905 if server_version >= 90500 906 order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])] 907 # :nocov: 908 else 909 range = 0...32 910 order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])] 911 # :nocov: 912 end 913 914 attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey]) 915 916 ds = metadata_dataset. 917 from{pg_class.as(:tab)}. 918 join(Sequel[:pg_index].as(:ind), :indrelid=>:oid). 919 join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid). 920 join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums). 921 left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]). 922 where{{ 923 indc[:relkind]=>%w'i I', 924 ind[:indisprimary]=>false, 925 :indexprs=>nil, 926 :indisvalid=>true}}. 927 order(*order). 928 select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]} 929 930 # :nocov: 931 ds = ds.where(:indisready=>true) if server_version >= 80300 932 ds = ds.where(:indislive=>true) if server_version >= 90300 933 # :nocov: 934 935 ds 936 end 937 end
Dataset
used to retrieve foreign keys referencing a table
# File lib/sequel/adapters/shared/postgres.rb 842 def _reverse_foreign_key_list_ds 843 @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true) 844 end
Dataset
used to get schema for tables
# File lib/sequel/adapters/shared/postgres.rb 1000 def _schema_ds 1001 @_schema_ds ||= begin 1002 ds = metadata_dataset.select{[ 1003 pg_attribute[:attname].as(:name), 1004 SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid), 1005 SQL::Cast.new(basetype[:oid], :integer).as(:base_oid), 1006 SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type), 1007 SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type), 1008 SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default), 1009 SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null), 1010 SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key), 1011 Sequel[:pg_type][:typtype], 1012 (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array), 1013 ]}. 1014 from(:pg_class). 1015 join(:pg_attribute, :attrelid=>:oid). 1016 join(:pg_type, :oid=>:atttypid). 1017 left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype). 1018 left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]). 1019 left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]). 1020 left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true). 1021 where{{pg_attribute[:attisdropped]=>false}}. 1022 where{pg_attribute[:attnum] > 0}. 1023 order{pg_attribute[:attnum]} 1024 1025 # :nocov: 1026 if server_version > 100000 1027 # :nocov: 1028 ds = ds.select_append{pg_attribute[:attidentity]} 1029 1030 # :nocov: 1031 if server_version > 120000 1032 # :nocov: 1033 ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)} 1034 end 1035 end 1036 1037 ds 1038 end 1039 end
Dataset
used to determine custom serial sequences for tables
# File lib/sequel/adapters/shared/postgres.rb 940 def _select_custom_sequence_ds 941 @_select_custom_sequence_ds ||= metadata_dataset. 942 from{pg_class.as(:t)}. 943 join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name). 944 join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr). 945 join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def). 946 join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons). 947 where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}. 948 select{ 949 expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2) 950 [ 951 name[:nspname].as(:schema), 952 Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence) 953 ] 954 } 955 end
Dataset
used to determine primary keys for tables
# File lib/sequel/adapters/shared/postgres.rb 986 def _select_pk_ds 987 @_select_pk_ds ||= metadata_dataset. 988 from(:pg_class, :pg_attribute, :pg_index, :pg_namespace). 989 where{[ 990 [pg_class[:oid], pg_attribute[:attrelid]], 991 [pg_class[:relnamespace], pg_namespace[:oid]], 992 [pg_class[:oid], pg_index[:indrelid]], 993 [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]], 994 [pg_index[:indisprimary], 't'] 995 ]}. 996 select{pg_attribute[:attname].as(:pk)} 997 end
Dataset
used to determine normal serial sequences for tables
# File lib/sequel/adapters/shared/postgres.rb 958 def _select_serial_sequence_ds 959 @_serial_sequence_ds ||= metadata_dataset. 960 from{[ 961 pg_class.as(:seq), 962 pg_attribute.as(:attr), 963 pg_depend.as(:dep), 964 pg_namespace.as(:name), 965 pg_constraint.as(:cons), 966 pg_class.as(:t) 967 ]}. 968 where{[ 969 [seq[:oid], dep[:objid]], 970 [seq[:relnamespace], name[:oid]], 971 [seq[:relkind], 'S'], 972 [attr[:attrelid], dep[:refobjid]], 973 [attr[:attnum], dep[:refobjsubid]], 974 [attr[:attrelid], cons[:conrelid]], 975 [attr[:attnum], cons[:conkey].sql_subscript(1)], 976 [attr[:attrelid], t[:oid]], 977 [cons[:contype], 'p'] 978 ]}. 979 select{[ 980 name[:nspname].as(:schema), 981 seq[:relname].as(:sequence) 982 ]} 983 end
# File lib/sequel/adapters/shared/postgres.rb 1041 def alter_table_add_column_sql(table, op) 1042 "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}" 1043 end
# File lib/sequel/adapters/shared/postgres.rb 1059 def alter_table_drop_column_sql(table, op) 1060 "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}" 1061 end
# File lib/sequel/adapters/shared/postgres.rb 1045 def alter_table_generator_class 1046 Postgres::AlterTableGenerator 1047 end
# File lib/sequel/adapters/shared/postgres.rb 1049 def alter_table_set_column_type_sql(table, op) 1050 s = super 1051 if using = op[:using] 1052 using = Sequel::LiteralString.new(using) if using.is_a?(String) 1053 s += ' USING ' 1054 s << literal(using) 1055 end 1056 s 1057 end
# File lib/sequel/adapters/shared/postgres.rb 1063 def alter_table_validate_constraint_sql(table, op) 1064 "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}" 1065 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 1070 def begin_new_transaction(conn, opts) 1071 super 1072 if opts.has_key?(:synchronous) 1073 case sync = opts[:synchronous] 1074 when true 1075 sync = :on 1076 when false 1077 sync = :off 1078 when nil 1079 return 1080 end 1081 1082 log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}") 1083 end 1084 end
Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.
# File lib/sequel/adapters/shared/postgres.rb 1087 def begin_savepoint(conn, opts) 1088 super 1089 1090 unless (read_only = opts[:read_only]).nil? 1091 log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}") 1092 end 1093 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 1097 def column_definition_collate_sql(sql, column) 1098 if collate = column[:collate] 1099 collate = literal(collate) unless collate.is_a?(String) 1100 sql << " COLLATE #{collate}" 1101 end 1102 end
Support identity columns, but only use the identity SQL
syntax if no default value is given.
# File lib/sequel/adapters/shared/postgres.rb 1106 def column_definition_default_sql(sql, column) 1107 super 1108 if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default] 1109 if (identity = column[:identity]) 1110 sql << " GENERATED " 1111 sql << (identity == :always ? "ALWAYS" : "BY DEFAULT") 1112 sql << " AS IDENTITY" 1113 elsif (generated = column[:generated_always_as]) 1114 sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED" 1115 end 1116 end 1117 end
Handle PostgreSQL specific default format.
# File lib/sequel/adapters/shared/postgres.rb 1120 def column_schema_normalize_default(default, type) 1121 if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default) 1122 default = m[1] || m[2] 1123 end 1124 super(default, type) 1125 end
PostgreSQL can't combine rename_column operations, and it can combine the custom validate_constraint operation.
# File lib/sequel/adapters/shared/postgres.rb 1139 def combinable_alter_table_op?(op) 1140 (super || op[:op] == :validate_constraint) && op[:op] != :rename_column 1141 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 1129 def commit_transaction(conn, opts=OPTS) 1130 if (s = opts[:prepare]) && savepoint_level(conn) <= 1 1131 log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}") 1132 else 1133 super 1134 end 1135 end
The SQL
queries to execute when starting a new connection.
# File lib/sequel/adapters/shared/postgres.rb 1145 def connection_configuration_sqls(opts=@opts) 1146 sqls = [] 1147 1148 sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true)) 1149 1150 cmm = opts.fetch(:client_min_messages, :warning) 1151 if cmm && !cmm.to_s.empty? 1152 cmm = cmm.to_s.upcase.strip 1153 unless VALID_CLIENT_MIN_MESSAGES.include?(cmm) 1154 raise Error, "Unsupported client_min_messages setting: #{cmm}" 1155 end 1156 sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'" 1157 end 1158 1159 if search_path = opts[:search_path] 1160 case search_path 1161 when String 1162 search_path = search_path.split(",").map(&:strip) 1163 when Array 1164 # nil 1165 else 1166 raise Error, "unrecognized value for :search_path option: #{search_path.inspect}" 1167 end 1168 sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}" 1169 end 1170 1171 sqls 1172 end
Handle exclusion constraints.
# File lib/sequel/adapters/shared/postgres.rb 1175 def constraint_definition_sql(constraint) 1176 case constraint[:type] 1177 when :exclude 1178 elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ') 1179 sql = String.new 1180 sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}" 1181 constraint_deferrable_sql_append(sql, constraint[:deferrable]) 1182 sql 1183 when :foreign_key, :check 1184 sql = super 1185 if constraint[:not_valid] 1186 sql << " NOT VALID" 1187 end 1188 sql 1189 else 1190 super 1191 end 1192 end
SQL
for doing fast table insert from stdin.
# File lib/sequel/adapters/shared/postgres.rb 1223 def copy_into_sql(table, opts) 1224 sql = String.new 1225 sql << "COPY #{literal(table)}" 1226 if cols = opts[:columns] 1227 sql << literal(Array(cols)) 1228 end 1229 sql << " FROM STDIN" 1230 if opts[:options] || opts[:format] 1231 sql << " (" 1232 sql << "FORMAT #{opts[:format]}" if opts[:format] 1233 sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1234 sql << ')' 1235 end 1236 sql 1237 end
SQL
for doing fast table output to stdout.
# File lib/sequel/adapters/shared/postgres.rb 1240 def copy_table_sql(table, opts) 1241 if table.is_a?(String) 1242 table 1243 else 1244 if opts[:options] || opts[:format] 1245 options = String.new 1246 options << " (" 1247 options << "FORMAT #{opts[:format]}" if opts[:format] 1248 options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1249 options << ')' 1250 end 1251 table = if table.is_a?(::Sequel::Dataset) 1252 "(#{table.sql})" 1253 else 1254 literal(table) 1255 end 1256 "COPY #{table} TO STDOUT#{options}" 1257 end 1258 end
SQL
statement to create database function.
# File lib/sequel/adapters/shared/postgres.rb 1261 def create_function_sql(name, definition, opts=OPTS) 1262 args = opts[:args] 1263 if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)} 1264 returns = opts[:returns] || 'void' 1265 end 1266 language = opts[:language] || 'SQL' 1267 <<-END 1268 CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)} 1269 #{"RETURNS #{returns}" if returns} 1270 LANGUAGE #{language} 1271 #{opts[:behavior].to_s.upcase if opts[:behavior]} 1272 #{'STRICT' if opts[:strict]} 1273 #{'SECURITY DEFINER' if opts[:security_definer]} 1274 #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]} 1275 #{"COST #{opts[:cost]}" if opts[:cost]} 1276 #{"ROWS #{opts[:rows]}" if opts[:rows]} 1277 #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]} 1278 AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]} 1279 END 1280 end
SQL
for creating a procedural language.
# File lib/sequel/adapters/shared/postgres.rb 1283 def create_language_sql(name, opts=OPTS) 1284 "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]}" 1285 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 1289 def create_partition_of_table_from_generator(name, generator, options) 1290 execute_ddl(create_partition_of_table_sql(name, generator, options)) 1291 end
SQL
for creating a partition of another table.
# File lib/sequel/adapters/shared/postgres.rb 1294 def create_partition_of_table_sql(name, generator, options) 1295 sql = create_table_prefix_sql(name, options).dup 1296 1297 sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}" 1298 1299 case generator.partition_type 1300 when :range 1301 from, to = generator.range 1302 sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}" 1303 when :list 1304 sql << " FOR VALUES IN #{literal(generator.list)}" 1305 when :hash 1306 mod, remainder = generator.hash_values 1307 sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})" 1308 else # when :default 1309 sql << " DEFAULT" 1310 end 1311 1312 sql << create_table_suffix_sql(name, options) 1313 1314 sql 1315 end
SQL
for creating a schema.
# File lib/sequel/adapters/shared/postgres.rb 1318 def create_schema_sql(name, opts=OPTS) 1319 "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}" 1320 end
# File lib/sequel/adapters/shared/postgres.rb 1376 def create_table_as_sql(name, sql, options) 1377 result = create_table_prefix_sql name, options 1378 if on_commit = options[:on_commit] 1379 result += " ON COMMIT #{ON_COMMIT[on_commit]}" 1380 end 1381 result += " AS #{sql}" 1382 end
# File lib/sequel/adapters/shared/postgres.rb 1384 def create_table_generator_class 1385 Postgres::CreateTableGenerator 1386 end
DDL statement for creating a table with the given name, columns, and options
# File lib/sequel/adapters/shared/postgres.rb 1323 def create_table_prefix_sql(name, options) 1324 prefix_sql = if options[:temp] 1325 raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged] 1326 raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign] 1327 temporary_table_sql 1328 elsif options[:foreign] 1329 raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged] 1330 'FOREIGN ' 1331 elsif options[:unlogged] 1332 'UNLOGGED ' 1333 end 1334 1335 "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}" 1336 end
SQL
for creating a table with PostgreSQL specific options
# File lib/sequel/adapters/shared/postgres.rb 1339 def create_table_sql(name, generator, options) 1340 "#{super}#{create_table_suffix_sql(name, options)}" 1341 end
Handle various PostgreSQl specific table extensions such as inheritance, partitioning, tablespaces, and foreign tables.
# File lib/sequel/adapters/shared/postgres.rb 1345 def create_table_suffix_sql(name, options) 1346 sql = String.new 1347 1348 if inherits = options[:inherits] 1349 sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})" 1350 end 1351 1352 if partition_by = options[:partition_by] 1353 sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}" 1354 end 1355 1356 if on_commit = options[:on_commit] 1357 raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp] 1358 raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit) 1359 sql << " ON COMMIT #{ON_COMMIT[on_commit]}" 1360 end 1361 1362 if tablespace = options[:tablespace] 1363 sql << " TABLESPACE #{quote_identifier(tablespace)}" 1364 end 1365 1366 if server = options[:foreign] 1367 sql << " SERVER #{quote_identifier(server)}" 1368 if foreign_opts = options[:options] 1369 sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})" 1370 end 1371 end 1372 1373 sql 1374 end
SQL
for creating a database trigger.
# File lib/sequel/adapters/shared/postgres.rb 1389 def create_trigger_sql(table, name, function, opts=OPTS) 1390 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 1391 whence = opts[:after] ? 'AFTER' : 'BEFORE' 1392 if filter = opts[:when] 1393 raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions? 1394 filter = " WHEN #{filter_expr(filter)}" 1395 end 1396 "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(', ')})" 1397 end
DDL fragment for initial part of CREATE VIEW statement
# File lib/sequel/adapters/shared/postgres.rb 1400 def create_view_prefix_sql(name, options) 1401 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]) 1402 1403 if options[:security_invoker] 1404 sql += " WITH (security_invoker)" 1405 end 1406 1407 if tablespace = options[:tablespace] 1408 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1409 end 1410 1411 sql 1412 end
# File lib/sequel/adapters/shared/postgres.rb 1218 def database_error_regexps 1219 DATABASE_ERROR_REGEXPS 1220 end
# File lib/sequel/adapters/shared/postgres.rb 1194 def database_specific_error_class_from_sqlstate(sqlstate) 1195 if sqlstate == '23P01' 1196 ExclusionConstraintViolation 1197 elsif sqlstate == '40P01' 1198 SerializationFailure 1199 elsif sqlstate == '55P03' 1200 DatabaseLockTimeout 1201 else 1202 super 1203 end 1204 end
SQL
for dropping a function from the database.
# File lib/sequel/adapters/shared/postgres.rb 1415 def drop_function_sql(name, opts=OPTS) 1416 "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}" 1417 end
Support :if_exists, :cascade, and :concurrently options.
# File lib/sequel/adapters/shared/postgres.rb 1420 def drop_index_sql(table, op) 1421 sch, _ = schema_and_table(table) 1422 "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]}" 1423 end
SQL
for dropping a procedural language from the database.
# File lib/sequel/adapters/shared/postgres.rb 1426 def drop_language_sql(name, opts=OPTS) 1427 "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}" 1428 end
SQL
for dropping a schema from the database.
# File lib/sequel/adapters/shared/postgres.rb 1431 def drop_schema_sql(name, opts=OPTS) 1432 "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}" 1433 end
Support :foreign tables
# File lib/sequel/adapters/shared/postgres.rb 1441 def drop_table_sql(name, options) 1442 "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}" 1443 end
SQL
for dropping a trigger from the database.
# File lib/sequel/adapters/shared/postgres.rb 1436 def drop_trigger_sql(table, name, opts=OPTS) 1437 "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}" 1438 end
SQL
for dropping a view from the database.
# File lib/sequel/adapters/shared/postgres.rb 1446 def drop_view_sql(name, opts=OPTS) 1447 "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}" 1448 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 1452 def filter_schema(ds, opts) 1453 expr = if schema = opts[:schema] 1454 schema.to_s 1455 else 1456 Sequel.function(:any, Sequel.function(:current_schemas, false)) 1457 end 1458 ds.where{{pg_namespace[:nspname]=>expr}} 1459 end
# File lib/sequel/adapters/shared/postgres.rb 1461 def index_definition_sql(table_name, index) 1462 cols = index[:columns] 1463 index_name = index[:name] || default_index_name(table_name, cols) 1464 1465 expr = if o = index[:opclass] 1466 "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})" 1467 else 1468 literal(Array(cols)) 1469 end 1470 1471 if_not_exists = " IF NOT EXISTS" if index[:if_not_exists] 1472 unique = "UNIQUE " if index[:unique] 1473 index_type = index[:type] 1474 filter = index[:where] || index[:filter] 1475 filter = " WHERE #{filter_expr(filter)}" if filter 1476 nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil? 1477 1478 case index_type 1479 when :full_text 1480 expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))" 1481 index_type = index[:index_type] || :gin 1482 when :spatial 1483 index_type = :gist 1484 end 1485 1486 "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}" 1487 end
Setup datastructures shared by all postgres adapters.
# File lib/sequel/adapters/shared/postgres.rb 1490 def initialize_postgres_adapter 1491 @primary_keys = {} 1492 @primary_key_sequences = {} 1493 @supported_types = {} 1494 procs = @conversion_procs = CONVERSION_PROCS.dup 1495 procs[1184] = procs[1114] = method(:to_application_timestamp) 1496 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/postgres.rb 1499 def pg_class_relname(type, opts) 1500 ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace) 1501 ds = filter_schema(ds, opts) 1502 m = output_identifier_meth 1503 if defined?(yield) 1504 yield(ds) 1505 elsif opts[:qualify] 1506 ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)} 1507 else 1508 ds.map{|r| m.call(r[:relname])} 1509 end 1510 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 1514 def regclass_oid(expr, opts=OPTS) 1515 if expr.is_a?(String) && !expr.is_a?(LiteralString) 1516 expr = Sequel.identifier(expr) 1517 end 1518 1519 sch, table = schema_and_table(expr) 1520 sch ||= opts[:schema] 1521 if sch 1522 expr = Sequel.qualify(sch, table) 1523 end 1524 1525 expr = if ds = opts[:dataset] 1526 ds.literal(expr) 1527 else 1528 literal(expr) 1529 end 1530 1531 Sequel.cast(expr.to_s,:regclass).cast(:oid) 1532 end
Remove the cached entries for primary keys and sequences when a table is changed.
# File lib/sequel/adapters/shared/postgres.rb 1535 def remove_cached_schema(table) 1536 tab = quote_schema_table(table) 1537 Sequel.synchronize do 1538 @primary_keys.delete(tab) 1539 @primary_key_sequences.delete(tab) 1540 end 1541 super 1542 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 1546 def rename_table_sql(name, new_name) 1547 "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}" 1548 end
The schema :type entry to use for array types.
# File lib/sequel/adapters/shared/postgres.rb 1563 def schema_array_type(db_type) 1564 :array 1565 end
Handle interval and citext types.
# File lib/sequel/adapters/shared/postgres.rb 1551 def schema_column_type(db_type) 1552 case db_type 1553 when /\Ainterval\z/io 1554 :interval 1555 when /\Acitext\z/io 1556 :string 1557 else 1558 super 1559 end 1560 end
The schema :type entry to use for row/composite types.
# File lib/sequel/adapters/shared/postgres.rb 1568 def schema_composite_type(db_type) 1569 :composite 1570 end
The schema :type entry to use for enum types.
# File lib/sequel/adapters/shared/postgres.rb 1573 def schema_enum_type(db_type) 1574 :enum 1575 end
The schema :type entry to use for multirange types.
# File lib/sequel/adapters/shared/postgres.rb 1583 def schema_multirange_type(db_type) 1584 :multirange 1585 end
The dataset used for parsing table schemas, using the pg_* system catalogs.
# File lib/sequel/adapters/shared/postgres.rb 1600 def schema_parse_table(table_name, opts) 1601 m = output_identifier_meth(opts[:dataset]) 1602 1603 _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row| 1604 row[:default] = nil if blank_object?(row[:default]) 1605 if row[:base_oid] 1606 row[:domain_oid] = row[:oid] 1607 row[:oid] = row.delete(:base_oid) 1608 row[:db_domain_type] = row[:db_type] 1609 row[:db_type] = row.delete(:db_base_type) 1610 else 1611 row.delete(:base_oid) 1612 row.delete(:db_base_type) 1613 end 1614 1615 db_type = row[:db_type] 1616 row[:type] = if row.delete(:is_array) 1617 schema_array_type(db_type) 1618 else 1619 send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type) 1620 end 1621 identity = row.delete(:attidentity) 1622 if row[:primary_key] 1623 row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd' 1624 end 1625 1626 # :nocov: 1627 if server_version >= 90600 1628 # :nocov: 1629 case row[:oid] 1630 when 1082 1631 row[:min_value] = MIN_DATE 1632 row[:max_value] = MAX_DATE 1633 when 1184, 1114 1634 if Sequel.datetime_class == Time 1635 row[:min_value] = MIN_TIMESTAMP 1636 row[:max_value] = MAX_TIMESTAMP 1637 end 1638 end 1639 end 1640 1641 [m.call(row.delete(:name)), row] 1642 end 1643 end
The schema :type entry to use for range types.
# File lib/sequel/adapters/shared/postgres.rb 1578 def schema_range_type(db_type) 1579 :range 1580 end
Set the transaction isolation level on the given connection
# File lib/sequel/adapters/shared/postgres.rb 1646 def set_transaction_isolation(conn, opts) 1647 level = opts.fetch(:isolation, transaction_isolation_level) 1648 read_only = opts[:read_only] 1649 deferrable = opts[:deferrable] 1650 if level || !read_only.nil? || !deferrable.nil? 1651 sql = String.new 1652 sql << "SET TRANSACTION" 1653 sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level 1654 sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil? 1655 sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil? 1656 log_connection_execute(conn, sql) 1657 end 1658 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 1661 def sql_function_args(args) 1662 "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})" 1663 end
PostgreSQL can combine multiple alter table ops into a single query.
# File lib/sequel/adapters/shared/postgres.rb 1666 def supports_combining_alter_table_ops? 1667 true 1668 end
PostgreSQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/postgres.rb 1671 def supports_create_or_replace_view? 1672 true 1673 end
Handle bigserial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1676 def type_literal_generic_bignum_symbol(column) 1677 column[:serial] ? :bigserial : super 1678 end
PostgreSQL uses the bytea data type for blobs
# File lib/sequel/adapters/shared/postgres.rb 1681 def type_literal_generic_file(column) 1682 :bytea 1683 end
Handle serial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1686 def type_literal_generic_integer(column) 1687 column[:serial] ? :serial : super 1688 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 1694 def type_literal_generic_string(column) 1695 if column[:text] 1696 :text 1697 elsif column[:fixed] 1698 "char(#{column[:size]||default_string_column_size})" 1699 elsif column[:text] == false || column[:size] 1700 "varchar(#{column[:size]||default_string_column_size})" 1701 else 1702 :text 1703 end 1704 end
PostgreSQL 9.4+ supports views with check option.
# File lib/sequel/adapters/shared/postgres.rb 1707 def view_with_check_option_support 1708 # :nocov: 1709 :local if server_version >= 90400 1710 # :nocov: 1711 end