# 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
Source
# File lib/sequel/adapters/shared/postgres.rb 309 def add_conversion_proc(oid, callable=nil, &block) 310 conversion_procs[oid] = callable || block 311 end
Set a conversion proc for the given oid. The callable can be passed either as a argument or a block.
Source
# 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
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.
Source
# 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
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.
Source
# 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
Source
# 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
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
Source
# 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
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.
Source
# File lib/sequel/adapters/shared/postgres.rb 448 def create_language(name, opts=OPTS) 449 self << create_language_sql(name, opts) 450 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.
Source
# File lib/sequel/adapters/shared/postgres.rb 457 def create_schema(name, opts=OPTS) 458 self << create_schema_sql(name, opts) 459 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)
Source
# 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.
Source
# 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
Support partitions of tables using the :partition_of option.
Source
# 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
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
Source
# File lib/sequel/adapters/shared/postgres.rb 497 def database_type 498 :postgres 499 end
Source
# File lib/sequel/adapters/shared/postgres.rb 516 def defer_constraints(opts=OPTS) 517 _set_constraints(' DEFERRED', opts) 518 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
Source
# 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
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.
Source
# File lib/sequel/adapters/shared/postgres.rb 536 def drop_function(name, opts=OPTS) 537 self << drop_function_sql(name, opts) 538 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.
Source
# 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 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.
Source
# 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 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.
Source
# 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
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.
Source
# 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
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.
Source
# 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
Source
# File lib/sequel/adapters/shared/postgres.rb 647 def immediate_constraints(opts=OPTS) 648 _set_constraints(' IMMEDIATE', opts) 649 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
Source
# 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
Use the pg_* system tables to determine indexes on a table
Source
# 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
Dataset
containing all current database locks
Source
# 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
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.
Source
Return primary key for the given table.
Source
# 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
Return the sequence providing the default for the primary key for the given table.
Source
# 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
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
Source
# 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
Reset the primary key sequence for the given table, basing it on the maximum current value of the table’s primary key.
Source
# 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
Source
# 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
PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.
Source
# 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
The version of the PostgreSQL server, used for determining capability.
Source
# File lib/sequel/adapters/shared/postgres.rb 765 def supports_create_table_if_not_exists? 766 server_version >= 90100 767 end
PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+
Source
# File lib/sequel/adapters/shared/postgres.rb 770 def supports_deferrable_constraints? 771 server_version >= 90000 772 end
PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.
Source
# File lib/sequel/adapters/shared/postgres.rb 775 def supports_deferrable_foreign_key_constraints? 776 true 777 end
PostgreSQL supports deferrable foreign key constraints.
Source
# File lib/sequel/adapters/shared/postgres.rb 780 def supports_drop_table_if_exists? 781 true 782 end
PostgreSQL supports DROP TABLE IF EXISTS
Source
# File lib/sequel/adapters/shared/postgres.rb 785 def supports_partial_indexes? 786 true 787 end
PostgreSQL supports partial indexes.
Source
# 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 prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.
Source
# File lib/sequel/adapters/shared/postgres.rb 802 def supports_savepoints? 803 true 804 end
PostgreSQL supports savepoints
Source
# File lib/sequel/adapters/shared/postgres.rb 807 def supports_transaction_isolation_levels? 808 true 809 end
PostgreSQL supports transaction isolation levels
Source
# File lib/sequel/adapters/shared/postgres.rb 812 def supports_transactional_ddl? 813 true 814 end
PostgreSQL supports transaction DDL statements.
Source
# File lib/sequel/adapters/shared/postgres.rb 790 def supports_trigger_conditions? 791 server_version >= 90000 792 end
PostgreSQL 9.0+ supports trigger conditions.
Source
# File lib/sequel/adapters/shared/postgres.rb 825 def tables(opts=OPTS, &block) 826 pg_class_relname(['r', 'p'], opts, &block) 827 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
Source
# 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
Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.
Source
# 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
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
Source
# 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
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
Source
# File lib/sequel/adapters/shared/postgres.rb 873 def with_advisory_lock(lock_id, opts=OPTS) 874 ds = dataset 875 if server = opts[:server] 876 ds = ds.server(server) 877 end 878 879 synchronize(server) do |c| 880 begin 881 if opts[:wait] 882 ds.get{pg_advisory_lock(lock_id)} 883 locked = true 884 else 885 unless locked = ds.get{pg_try_advisory_lock(lock_id)} 886 raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}" 887 end 888 end 889 890 yield 891 ensure 892 ds.get{pg_advisory_unlock(lock_id)} if locked 893 end 894 end 895 end
Attempt to acquire an exclusive advisory lock with the given lock_id (which should be a 64-bit integer). If successful, yield to the block, then release the advisory lock when the block exits. If unsuccessful, raise a Sequel::AdvisoryLockError.
DB.with_advisory_lock(1347){DB.get(1)} # SELECT pg_try_advisory_lock(1357) LIMIT 1 # SELECT 1 AS v LIMIT 1 # SELECT pg_advisory_unlock(1357) LIMIT 1
Options:
- :wait
-
Do not raise an error, instead, wait until the advisory lock can be acquired.
Private Instance Methods
Source
# File lib/sequel/adapters/shared/postgres.rb 919 def __foreign_key_list_ds(reverse) 920 if reverse 921 ctable = Sequel[:att2] 922 cclass = Sequel[:cl2] 923 rtable = Sequel[:att] 924 rclass = Sequel[:cl] 925 else 926 ctable = Sequel[:att] 927 cclass = Sequel[:cl] 928 rtable = Sequel[:att2] 929 rclass = Sequel[:cl2] 930 end 931 932 if server_version >= 90500 933 cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])} 934 rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])} 935 # :nocov: 936 else 937 range = 0...32 938 cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])} 939 rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])} 940 # :nocov: 941 end 942 943 ds = metadata_dataset. 944 from{pg_constraint.as(:co)}. 945 join(Sequel[:pg_class].as(cclass), :oid=>:conrelid). 946 join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 947 join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]). 948 join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])). 949 join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]). 950 order{[co[:conname], cpos]}. 951 where{{ 952 cl[:relkind]=>%w'r p', 953 co[:contype]=>'f', 954 cpos=>rpos 955 }}. 956 select{[ 957 co[:conname].as(:name), 958 ctable[:attname].as(:column), 959 co[:confupdtype].as(:on_update), 960 co[:confdeltype].as(:on_delete), 961 cl2[:relname].as(:table), 962 rtable[:attname].as(:refcolumn), 963 SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable), 964 nsp[:nspname].as(:schema) 965 ]} 966 967 if reverse 968 ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname]) 969 end 970 971 ds 972 end
Build dataset used for foreign key list methods.
Source
# File lib/sequel/adapters/shared/postgres.rb 900 def _check_constraints_ds 901 @_check_constraints_ds ||= metadata_dataset. 902 from{pg_constraint.as(:co)}. 903 left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 904 where(:contype=>'c'). 905 select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]} 906 end
Dataset
used to retrieve CHECK constraint information
Source
# File lib/sequel/adapters/shared/postgres.rb 909 def _foreign_key_list_ds 910 @_foreign_key_list_ds ||= __foreign_key_list_ds(false) 911 end
Dataset
used to retrieve foreign keys referenced by a table
Source
# File lib/sequel/adapters/shared/postgres.rb 975 def _indexes_ds 976 @_indexes_ds ||= begin 977 if server_version >= 90500 978 order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])] 979 # :nocov: 980 else 981 range = 0...32 982 order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])] 983 # :nocov: 984 end 985 986 attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey]) 987 988 ds = metadata_dataset. 989 from{pg_class.as(:tab)}. 990 join(Sequel[:pg_index].as(:ind), :indrelid=>:oid). 991 join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid). 992 join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums). 993 left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]). 994 where{{ 995 indc[:relkind]=>%w'i I', 996 ind[:indisprimary]=>false, 997 :indexprs=>nil, 998 :indisvalid=>true}}. 999 order(*order). 1000 select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]} 1001 1002 # :nocov: 1003 ds = ds.where(:indisready=>true) if server_version >= 80300 1004 ds = ds.where(:indislive=>true) if server_version >= 90300 1005 # :nocov: 1006 1007 ds 1008 end 1009 end
Dataset
used to retrieve index information
Source
# File lib/sequel/adapters/shared/postgres.rb 914 def _reverse_foreign_key_list_ds 915 @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true) 916 end
Dataset
used to retrieve foreign keys referencing a table
Source
# File lib/sequel/adapters/shared/postgres.rb 1072 def _schema_ds 1073 @_schema_ds ||= begin 1074 ds = metadata_dataset.select{[ 1075 pg_attribute[:attname].as(:name), 1076 SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid), 1077 SQL::Cast.new(basetype[:oid], :integer).as(:base_oid), 1078 SQL::Function.new(:col_description, pg_class[:oid], pg_attribute[:attnum]).as(:comment), 1079 SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type), 1080 SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type), 1081 SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default), 1082 SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null), 1083 SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key), 1084 Sequel[:pg_type][:typtype], 1085 (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array), 1086 ]}. 1087 from(:pg_class). 1088 join(:pg_attribute, :attrelid=>:oid). 1089 join(:pg_type, :oid=>:atttypid). 1090 left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype). 1091 left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]). 1092 left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]). 1093 left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true). 1094 where{{pg_attribute[:attisdropped]=>false}}. 1095 where{pg_attribute[:attnum] > 0}. 1096 order{pg_attribute[:attnum]} 1097 1098 # :nocov: 1099 if server_version > 100000 1100 # :nocov: 1101 ds = ds.select_append{pg_attribute[:attidentity]} 1102 1103 # :nocov: 1104 if server_version > 120000 1105 # :nocov: 1106 ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)} 1107 end 1108 end 1109 1110 ds 1111 end 1112 end
Dataset
used to get schema for tables
Source
# File lib/sequel/adapters/shared/postgres.rb 1012 def _select_custom_sequence_ds 1013 @_select_custom_sequence_ds ||= metadata_dataset. 1014 from{pg_class.as(:t)}. 1015 join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name). 1016 join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr). 1017 join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def). 1018 join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons). 1019 where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}. 1020 select{ 1021 expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2) 1022 [ 1023 name[:nspname].as(:schema), 1024 Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence) 1025 ] 1026 } 1027 end
Dataset
used to determine custom serial sequences for tables
Source
# File lib/sequel/adapters/shared/postgres.rb 1058 def _select_pk_ds 1059 @_select_pk_ds ||= metadata_dataset. 1060 from(:pg_class, :pg_attribute, :pg_index, :pg_namespace). 1061 where{[ 1062 [pg_class[:oid], pg_attribute[:attrelid]], 1063 [pg_class[:relnamespace], pg_namespace[:oid]], 1064 [pg_class[:oid], pg_index[:indrelid]], 1065 [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]], 1066 [pg_index[:indisprimary], 't'] 1067 ]}. 1068 select{pg_attribute[:attname].as(:pk)} 1069 end
Dataset
used to determine primary keys for tables
Source
# File lib/sequel/adapters/shared/postgres.rb 1030 def _select_serial_sequence_ds 1031 @_serial_sequence_ds ||= metadata_dataset. 1032 from{[ 1033 pg_class.as(:seq), 1034 pg_attribute.as(:attr), 1035 pg_depend.as(:dep), 1036 pg_namespace.as(:name), 1037 pg_constraint.as(:cons), 1038 pg_class.as(:t) 1039 ]}. 1040 where{[ 1041 [seq[:oid], dep[:objid]], 1042 [seq[:relnamespace], name[:oid]], 1043 [seq[:relkind], 'S'], 1044 [attr[:attrelid], dep[:refobjid]], 1045 [attr[:attnum], dep[:refobjsubid]], 1046 [attr[:attrelid], cons[:conrelid]], 1047 [attr[:attnum], cons[:conkey].sql_subscript(1)], 1048 [attr[:attrelid], t[:oid]], 1049 [cons[:contype], 'p'] 1050 ]}. 1051 select{[ 1052 name[:nspname].as(:schema), 1053 seq[:relname].as(:sequence) 1054 ]} 1055 end
Dataset
used to determine normal serial sequences for tables
Source
# File lib/sequel/adapters/shared/postgres.rb 1115 def _set_constraints(type, opts) 1116 execute_ddl(_set_constraints_sql(type, opts), opts) 1117 end
Internals of defer_constraints/immediate_constraints
Source
# File lib/sequel/adapters/shared/postgres.rb 1120 def _set_constraints_sql(type, opts) 1121 sql = String.new 1122 sql << "SET CONSTRAINTS " 1123 if constraints = opts[:constraints] 1124 dataset.send(:source_list_append, sql, Array(constraints)) 1125 else 1126 sql << "ALL" 1127 end 1128 sql << type 1129 end
SQL
to use for SET CONSTRAINTS
Source
# File lib/sequel/adapters/shared/postgres.rb 1133 def _table_exists?(ds) 1134 super 1135 rescue DatabaseError => e 1136 raise e unless /canceling statement due to (?:statement|lock) timeout/ =~ e.message 1137 end
Consider lock or statement timeout errors as evidence that the table exists but is locked.
Source
# File lib/sequel/adapters/shared/postgres.rb 1139 def alter_table_add_column_sql(table, op) 1140 "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}" 1141 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1157 def alter_table_drop_column_sql(table, op) 1158 "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}" 1159 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1143 def alter_table_generator_class 1144 Postgres::AlterTableGenerator 1145 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1147 def alter_table_set_column_type_sql(table, op) 1148 s = super 1149 if using = op[:using] 1150 using = Sequel::LiteralString.new(using) if using.is_a?(String) 1151 s += ' USING ' 1152 s << literal(using) 1153 end 1154 s 1155 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1161 def alter_table_validate_constraint_sql(table, op) 1162 "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}" 1163 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1168 def begin_new_transaction(conn, opts) 1169 super 1170 if opts.has_key?(:synchronous) 1171 case sync = opts[:synchronous] 1172 when true 1173 sync = :on 1174 when false 1175 sync = :off 1176 when nil 1177 return 1178 end 1179 1180 log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}") 1181 end 1182 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.
Source
# File lib/sequel/adapters/shared/postgres.rb 1185 def begin_savepoint(conn, opts) 1186 super 1187 1188 unless (read_only = opts[:read_only]).nil? 1189 log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}") 1190 end 1191 end
Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.
Source
# File lib/sequel/adapters/shared/postgres.rb 1195 def column_definition_collate_sql(sql, column) 1196 if collate = column[:collate] 1197 collate = literal(collate) unless collate.is_a?(String) 1198 sql << " COLLATE #{collate}" 1199 end 1200 end
Literalize non-String collate options. This is because unquoted collatations are folded to lowercase, and PostgreSQL used mixed case or capitalized collations.
Source
# File lib/sequel/adapters/shared/postgres.rb 1204 def column_definition_default_sql(sql, column) 1205 super 1206 if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default] 1207 if (identity = column[:identity]) 1208 sql << " GENERATED " 1209 sql << (identity == :always ? "ALWAYS" : "BY DEFAULT") 1210 sql << " AS IDENTITY" 1211 elsif (generated = column[:generated_always_as]) 1212 sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED" 1213 end 1214 end 1215 end
Support identity columns, but only use the identity SQL
syntax if no default value is given.
Source
# File lib/sequel/adapters/shared/postgres.rb 1218 def column_schema_normalize_default(default, type) 1219 if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default) 1220 default = m[1] || m[2] 1221 end 1222 super(default, type) 1223 end
Handle PostgreSQL specific default format.
Source
# File lib/sequel/adapters/shared/postgres.rb 1237 def combinable_alter_table_op?(op) 1238 (super || op[:op] == :validate_constraint) && op[:op] != :rename_column 1239 end
PostgreSQL can’t combine rename_column operations, and it can combine the custom validate_constraint operation.
Source
# File lib/sequel/adapters/shared/postgres.rb 1227 def commit_transaction(conn, opts=OPTS) 1228 if (s = opts[:prepare]) && savepoint_level(conn) <= 1 1229 log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}") 1230 else 1231 super 1232 end 1233 end
If the :prepare option is given and we aren’t in a savepoint, prepare the transaction for a two-phase commit.
Source
# File lib/sequel/adapters/shared/postgres.rb 1243 def connection_configuration_sqls(opts=@opts) 1244 sqls = [] 1245 1246 sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true)) 1247 1248 cmm = opts.fetch(:client_min_messages, :warning) 1249 if cmm && !cmm.to_s.empty? 1250 cmm = cmm.to_s.upcase.strip 1251 unless VALID_CLIENT_MIN_MESSAGES.include?(cmm) 1252 raise Error, "Unsupported client_min_messages setting: #{cmm}" 1253 end 1254 sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'" 1255 end 1256 1257 if search_path = opts[:search_path] 1258 case search_path 1259 when String 1260 search_path = search_path.split(",").map(&:strip) 1261 when Array 1262 # nil 1263 else 1264 raise Error, "unrecognized value for :search_path option: #{search_path.inspect}" 1265 end 1266 sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}" 1267 end 1268 1269 sqls 1270 end
The SQL
queries to execute when starting a new connection.
Source
# File lib/sequel/adapters/shared/postgres.rb 1273 def constraint_definition_sql(constraint) 1274 case type = constraint[:type] 1275 when :exclude 1276 elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ') 1277 sql = String.new 1278 sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}" 1279 constraint_deferrable_sql_append(sql, constraint[:deferrable]) 1280 sql 1281 when :primary_key, :unique 1282 if using_index = constraint[:using_index] 1283 sql = String.new 1284 sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name] 1285 if type == :primary_key 1286 sql << primary_key_constraint_sql_fragment(constraint) 1287 else 1288 sql << unique_constraint_sql_fragment(constraint) 1289 end 1290 sql << " USING INDEX " << quote_identifier(using_index) 1291 else 1292 super 1293 end 1294 else # when :foreign_key, :check 1295 sql = super 1296 if constraint[:not_valid] 1297 sql << " NOT VALID" 1298 end 1299 sql 1300 end 1301 end
Handle exclusion constraints.
Source
# File lib/sequel/adapters/shared/postgres.rb 1332 def copy_into_sql(table, opts) 1333 sql = String.new 1334 sql << "COPY #{literal(table)}" 1335 if cols = opts[:columns] 1336 sql << literal(Array(cols)) 1337 end 1338 sql << " FROM STDIN" 1339 if opts[:options] || opts[:format] 1340 sql << " (" 1341 sql << "FORMAT #{opts[:format]}" if opts[:format] 1342 sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1343 sql << ')' 1344 end 1345 sql 1346 end
SQL
for doing fast table insert from stdin.
Source
# File lib/sequel/adapters/shared/postgres.rb 1349 def copy_table_sql(table, opts) 1350 if table.is_a?(String) 1351 table 1352 else 1353 if opts[:options] || opts[:format] 1354 options = String.new 1355 options << " (" 1356 options << "FORMAT #{opts[:format]}" if opts[:format] 1357 options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1358 options << ')' 1359 end 1360 table = if table.is_a?(::Sequel::Dataset) 1361 "(#{table.sql})" 1362 else 1363 literal(table) 1364 end 1365 "COPY #{table} TO STDOUT#{options}" 1366 end 1367 end
SQL
for doing fast table output to stdout.
Source
# File lib/sequel/adapters/shared/postgres.rb 1370 def create_function_sql(name, definition, opts=OPTS) 1371 args = opts[:args] 1372 if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)} 1373 returns = opts[:returns] || 'void' 1374 end 1375 language = opts[:language] || 'SQL' 1376 <<-END 1377 CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)} 1378 #{"RETURNS #{returns}" if returns} 1379 LANGUAGE #{language} 1380 #{opts[:behavior].to_s.upcase if opts[:behavior]} 1381 #{'STRICT' if opts[:strict]} 1382 #{'SECURITY DEFINER' if opts[:security_definer]} 1383 #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]} 1384 #{"COST #{opts[:cost]}" if opts[:cost]} 1385 #{"ROWS #{opts[:rows]}" if opts[:rows]} 1386 #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]} 1387 AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]} 1388 END 1389 end
SQL
statement to create database function.
Source
# File lib/sequel/adapters/shared/postgres.rb 1392 def create_language_sql(name, opts=OPTS) 1393 "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]}" 1394 end
SQL
for creating a procedural language.
Source
# File lib/sequel/adapters/shared/postgres.rb 1398 def create_partition_of_table_from_generator(name, generator, options) 1399 execute_ddl(create_partition_of_table_sql(name, generator, options)) 1400 end
Create a partition of another table, used when the create_table
with the :partition_of option is given.
Source
# File lib/sequel/adapters/shared/postgres.rb 1403 def create_partition_of_table_sql(name, generator, options) 1404 sql = create_table_prefix_sql(name, options).dup 1405 1406 sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}" 1407 1408 case generator.partition_type 1409 when :range 1410 from, to = generator.range 1411 sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}" 1412 when :list 1413 sql << " FOR VALUES IN #{literal(generator.list)}" 1414 when :hash 1415 mod, remainder = generator.hash_values 1416 sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})" 1417 else # when :default 1418 sql << " DEFAULT" 1419 end 1420 1421 sql << create_table_suffix_sql(name, options) 1422 1423 sql 1424 end
SQL
for creating a partition of another table.
Source
# File lib/sequel/adapters/shared/postgres.rb 1427 def create_schema_sql(name, opts=OPTS) 1428 "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}" 1429 end
SQL
for creating a schema.
Source
# File lib/sequel/adapters/shared/postgres.rb 1485 def create_table_as_sql(name, sql, options) 1486 result = create_table_prefix_sql name, options 1487 if on_commit = options[:on_commit] 1488 result += " ON COMMIT #{ON_COMMIT[on_commit]}" 1489 end 1490 result += " AS #{sql}" 1491 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1493 def create_table_generator_class 1494 Postgres::CreateTableGenerator 1495 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1432 def create_table_prefix_sql(name, options) 1433 prefix_sql = if options[:temp] 1434 raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged] 1435 raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign] 1436 temporary_table_sql 1437 elsif options[:foreign] 1438 raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged] 1439 'FOREIGN ' 1440 elsif options.fetch(:unlogged){typecast_value_boolean(@opts[:unlogged_tables_default])} 1441 'UNLOGGED ' 1442 end 1443 1444 "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{create_table_table_name_sql(name, options)}" 1445 end
DDL statement for creating a table with the given name, columns, and options
Source
# File lib/sequel/adapters/shared/postgres.rb 1448 def create_table_sql(name, generator, options) 1449 "#{super}#{create_table_suffix_sql(name, options)}" 1450 end
SQL
for creating a table with PostgreSQL specific options
Source
# File lib/sequel/adapters/shared/postgres.rb 1454 def create_table_suffix_sql(name, options) 1455 sql = String.new 1456 1457 if inherits = options[:inherits] 1458 sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})" 1459 end 1460 1461 if partition_by = options[:partition_by] 1462 sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}" 1463 end 1464 1465 if on_commit = options[:on_commit] 1466 raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp] 1467 raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit) 1468 sql << " ON COMMIT #{ON_COMMIT[on_commit]}" 1469 end 1470 1471 if tablespace = options[:tablespace] 1472 sql << " TABLESPACE #{quote_identifier(tablespace)}" 1473 end 1474 1475 if server = options[:foreign] 1476 sql << " SERVER #{quote_identifier(server)}" 1477 if foreign_opts = options[:options] 1478 sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})" 1479 end 1480 end 1481 1482 sql 1483 end
Handle various PostgreSQl specific table extensions such as inheritance, partitioning, tablespaces, and foreign tables.
Source
# File lib/sequel/adapters/shared/postgres.rb 1498 def create_trigger_sql(table, name, function, opts=OPTS) 1499 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 1500 whence = opts[:after] ? 'AFTER' : 'BEFORE' 1501 if filter = opts[:when] 1502 raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions? 1503 filter = " WHEN #{filter_expr(filter)}" 1504 end 1505 "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(', ')})" 1506 end
SQL
for creating a database trigger.
Source
# File lib/sequel/adapters/shared/postgres.rb 1509 def create_view_prefix_sql(name, options) 1510 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]) 1511 1512 if options[:security_invoker] 1513 sql += " WITH (security_invoker)" 1514 end 1515 1516 if tablespace = options[:tablespace] 1517 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1518 end 1519 1520 sql 1521 end
DDL fragment for initial part of CREATE VIEW statement
Source
# File lib/sequel/adapters/shared/postgres.rb 1327 def database_error_regexps 1328 DATABASE_ERROR_REGEXPS 1329 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1303 def database_specific_error_class_from_sqlstate(sqlstate) 1304 if sqlstate == '23P01' 1305 ExclusionConstraintViolation 1306 elsif sqlstate == '40P01' 1307 SerializationFailure 1308 elsif sqlstate == '55P03' 1309 DatabaseLockTimeout 1310 else 1311 super 1312 end 1313 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1524 def drop_function_sql(name, opts=OPTS) 1525 "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}" 1526 end
SQL
for dropping a function from the database.
Source
# File lib/sequel/adapters/shared/postgres.rb 1529 def drop_index_sql(table, op) 1530 sch, _ = schema_and_table(table) 1531 "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]}" 1532 end
Support :if_exists, :cascade, and :concurrently options.
Source
# File lib/sequel/adapters/shared/postgres.rb 1535 def drop_language_sql(name, opts=OPTS) 1536 "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}" 1537 end
SQL
for dropping a procedural language from the database.
Source
# File lib/sequel/adapters/shared/postgres.rb 1540 def drop_schema_sql(name, opts=OPTS) 1541 "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}" 1542 end
SQL
for dropping a schema from the database.
Source
# File lib/sequel/adapters/shared/postgres.rb 1550 def drop_table_sql(name, options) 1551 "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}" 1552 end
Support :foreign tables
Source
# File lib/sequel/adapters/shared/postgres.rb 1545 def drop_trigger_sql(table, name, opts=OPTS) 1546 "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}" 1547 end
SQL
for dropping a trigger from the database.
Source
# File lib/sequel/adapters/shared/postgres.rb 1555 def drop_view_sql(name, opts=OPTS) 1556 "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}" 1557 end
SQL
for dropping a view from the database.
Source
# File lib/sequel/adapters/shared/postgres.rb 1561 def filter_schema(ds, opts) 1562 expr = if schema = opts[:schema] 1563 if schema.is_a?(SQL::Identifier) 1564 schema.value.to_s 1565 else 1566 schema.to_s 1567 end 1568 else 1569 Sequel.function(:any, Sequel.function(:current_schemas, false)) 1570 end 1571 ds.where{{pg_namespace[:nspname]=>expr}} 1572 end
If opts includes a :schema option, use it, otherwise restrict the filter to only the currently visible schemas.
Source
# File lib/sequel/adapters/shared/postgres.rb 1574 def index_definition_sql(table_name, index) 1575 cols = index[:columns] 1576 index_name = index[:name] || default_index_name(table_name, cols) 1577 1578 expr = if o = index[:opclass] 1579 "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})" 1580 else 1581 literal(Array(cols)) 1582 end 1583 1584 if_not_exists = " IF NOT EXISTS" if index[:if_not_exists] 1585 unique = "UNIQUE " if index[:unique] 1586 index_type = index[:type] 1587 filter = index[:where] || index[:filter] 1588 filter = " WHERE #{filter_expr(filter)}" if filter 1589 nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil? 1590 1591 case index_type 1592 when :full_text 1593 expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))" 1594 index_type = index[:index_type] || :gin 1595 when :spatial 1596 index_type = :gist 1597 end 1598 1599 "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}" 1600 end
Source
# File lib/sequel/adapters/shared/postgres.rb 1603 def initialize_postgres_adapter 1604 @primary_keys = {} 1605 @primary_key_sequences = {} 1606 @supported_types = {} 1607 procs = @conversion_procs = CONVERSION_PROCS.dup 1608 procs[1184] = procs[1114] = method(:to_application_timestamp) 1609 end
Setup datastructures shared by all postgres adapters.
Source
# File lib/sequel/adapters/shared/postgres.rb 1612 def pg_class_relname(type, opts) 1613 ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace) 1614 ds = filter_schema(ds, opts) 1615 m = output_identifier_meth 1616 if defined?(yield) 1617 yield(ds) 1618 elsif opts[:qualify] 1619 ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)} 1620 else 1621 ds.map{|r| m.call(r[:relname])} 1622 end 1623 end
Backbone of the tables and views support.
Source
# File lib/sequel/adapters/shared/postgres.rb 1627 def regclass_oid(expr, opts=OPTS) 1628 if expr.is_a?(String) && !expr.is_a?(LiteralString) 1629 expr = Sequel.identifier(expr) 1630 end 1631 1632 sch, table = schema_and_table(expr) 1633 sch ||= opts[:schema] 1634 if sch 1635 expr = Sequel.qualify(sch, table) 1636 end 1637 1638 expr = if ds = opts[:dataset] 1639 ds.literal(expr) 1640 else 1641 literal(expr) 1642 end 1643 1644 Sequel.cast(expr.to_s,:regclass).cast(:oid) 1645 end
Return an expression the oid for the table expr. Used by the metadata parsing code to disambiguate unqualified tables.
Source
# File lib/sequel/adapters/shared/postgres.rb 1648 def remove_cached_schema(table) 1649 tab = quote_schema_table(table) 1650 Sequel.synchronize do 1651 @primary_keys.delete(tab) 1652 @primary_key_sequences.delete(tab) 1653 end 1654 super 1655 end
Remove the cached entries for primary keys and sequences when a table is changed.
Source
# File lib/sequel/adapters/shared/postgres.rb 1659 def rename_table_sql(name, new_name) 1660 "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}" 1661 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.
Source
# File lib/sequel/adapters/shared/postgres.rb 1676 def schema_array_type(db_type) 1677 :array 1678 end
The schema :type entry to use for array types.
Source
# File lib/sequel/adapters/shared/postgres.rb 1664 def schema_column_type(db_type) 1665 case db_type 1666 when /\Ainterval\z/io 1667 :interval 1668 when /\Acitext\z/io 1669 :string 1670 else 1671 super 1672 end 1673 end
Handle interval and citext types.
Source
# File lib/sequel/adapters/shared/postgres.rb 1681 def schema_composite_type(db_type) 1682 :composite 1683 end
The schema :type entry to use for row/composite types.
Source
# File lib/sequel/adapters/shared/postgres.rb 1686 def schema_enum_type(db_type) 1687 :enum 1688 end
The schema :type entry to use for enum types.
Source
# File lib/sequel/adapters/shared/postgres.rb 1696 def schema_multirange_type(db_type) 1697 :multirange 1698 end
The schema :type entry to use for multirange types.
Source
# File lib/sequel/adapters/shared/postgres.rb 1713 def schema_parse_table(table_name, opts) 1714 m = output_identifier_meth(opts[:dataset]) 1715 1716 _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row| 1717 row[:default] = nil if blank_object?(row[:default]) 1718 if row[:base_oid] 1719 row[:domain_oid] = row[:oid] 1720 row[:oid] = row.delete(:base_oid) 1721 row[:db_domain_type] = row[:db_type] 1722 row[:db_type] = row.delete(:db_base_type) 1723 else 1724 row.delete(:base_oid) 1725 row.delete(:db_base_type) 1726 end 1727 1728 db_type = row[:db_type] 1729 row[:type] = if row.delete(:is_array) 1730 schema_array_type(db_type) 1731 else 1732 send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type) 1733 end 1734 identity = row.delete(:attidentity) 1735 if row[:primary_key] 1736 row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd' 1737 end 1738 1739 # :nocov: 1740 if server_version >= 90600 1741 # :nocov: 1742 case row[:oid] 1743 when 1082 1744 row[:min_value] = MIN_DATE 1745 row[:max_value] = MAX_DATE 1746 when 1184, 1114 1747 if Sequel.datetime_class == Time 1748 row[:min_value] = MIN_TIMESTAMP 1749 row[:max_value] = MAX_TIMESTAMP 1750 end 1751 end 1752 end 1753 1754 [m.call(row.delete(:name)), row] 1755 end 1756 end
The dataset used for parsing table schemas, using the pg_* system catalogs.
Source
# File lib/sequel/adapters/shared/postgres.rb 1691 def schema_range_type(db_type) 1692 :range 1693 end
The schema :type entry to use for range types.
Source
# File lib/sequel/adapters/shared/postgres.rb 1759 def set_transaction_isolation(conn, opts) 1760 level = opts.fetch(:isolation, transaction_isolation_level) 1761 read_only = opts[:read_only] 1762 deferrable = opts[:deferrable] 1763 if level || !read_only.nil? || !deferrable.nil? 1764 sql = String.new 1765 sql << "SET TRANSACTION" 1766 sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level 1767 sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil? 1768 sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil? 1769 log_connection_execute(conn, sql) 1770 end 1771 end
Set the transaction isolation level on the given connection
Source
# File lib/sequel/adapters/shared/postgres.rb 1774 def sql_function_args(args) 1775 "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})" 1776 end
Turns an array of argument specifiers into an SQL
fragment used for function arguments. See create_function_sql.
Source
# File lib/sequel/adapters/shared/postgres.rb 1779 def supports_combining_alter_table_ops? 1780 true 1781 end
PostgreSQL can combine multiple alter table ops into a single query.
Source
# File lib/sequel/adapters/shared/postgres.rb 1784 def supports_create_or_replace_view? 1785 true 1786 end
PostgreSQL supports CREATE OR REPLACE VIEW.
Source
# File lib/sequel/adapters/shared/postgres.rb 1789 def type_literal_generic_bignum_symbol(column) 1790 column[:serial] ? :bigserial : super 1791 end
Handle bigserial type if :serial option is present
Source
# File lib/sequel/adapters/shared/postgres.rb 1794 def type_literal_generic_file(column) 1795 :bytea 1796 end
PostgreSQL uses the bytea data type for blobs
Source
# File lib/sequel/adapters/shared/postgres.rb 1799 def type_literal_generic_integer(column) 1800 column[:serial] ? :serial : super 1801 end
Handle serial type if :serial option is present
Source
# File lib/sequel/adapters/shared/postgres.rb 1807 def type_literal_generic_string(column) 1808 if column[:text] 1809 :text 1810 elsif column[:fixed] 1811 "char(#{column[:size]||default_string_column_size})" 1812 elsif column[:text] == false || column[:size] 1813 "varchar(#{column[:size]||default_string_column_size})" 1814 else 1815 :text 1816 end 1817 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.
Source
# File lib/sequel/adapters/shared/postgres.rb 1820 def view_with_check_option_support 1821 # :nocov: 1822 :local if server_version >= 90400 1823 # :nocov: 1824 end
PostgreSQL 9.4+ supports views with check option.