# File lib/sequel/adapters/shared/postgres.rb 711 def primary_key(table, opts=OPTS) 712 quoted_table = quote_schema_table(table) 713 Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)} 714 sql = "#{SELECT_PK_SQL} AND pg_class.oid = #{literal(regclass_oid(table, opts))}" 715 value = fetch(sql).single_value 716 Sequel.synchronize{@primary_keys[quoted_table] = value} 717 end
module Sequel::Postgres::DatabaseMethods
Constants
- DATABASE_ERROR_REGEXPS
- FOREIGN_KEY_LIST_ON_DELETE_MAP
- ON_COMMIT
- PREPARED_ARG_PLACEHOLDER
- SELECT_CUSTOM_SEQUENCE_SQL
SQL
fragment for custom sequences (ones not created by serial primary key), Returning the schema and literal form of the sequence name, by parsing the column defaults table.- SELECT_PK_SQL
SQL
fragment for determining primary key column for the given table. Only returns the first primary key if the table has a composite primary key.- SELECT_SERIAL_SEQUENCE_SQL
SQL
fragment for getting sequence associated with table's primary key, assuming it was a serial primary key column.- VALID_CLIENT_MIN_MESSAGES
Attributes
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 296 def add_conversion_proc(oid, callable=nil, &block) 297 conversion_procs[oid] = callable || block 298 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 303 def add_named_conversion_proc(name, &block) 304 unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid) 305 raise Error, "No matching type in pg_type for #{name.inspect}" 306 end 307 add_conversion_proc(oid, block) 308 end
A hash of metadata for CHECK constraints on the table. Keys are CHECK constraint name symbols. Values are hashes with the following keys:
- :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 319 def check_constraints(table) 320 m = output_identifier_meth 321 322 rows = metadata_dataset. 323 from{pg_constraint.as(:co)}. 324 left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 325 where(:conrelid=>regclass_oid(table), :contype=>'c'). 326 select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]} 327 328 hash = {} 329 rows.each do |row| 330 constraint = m.call(row[:constraint]) 331 entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]} 332 entry[:columns] << m.call(row[:column]) if row[:column] 333 end 334 335 hash 336 end
# File lib/sequel/adapters/shared/postgres.rb 310 def commit_prepared_transaction(transaction_id, opts=OPTS) 311 run("COMMIT PREPARED #{literal(transaction_id)}", opts) 312 end
Convert 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 356 def convert_serial_to_identity(table, opts=OPTS) 357 raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002 358 359 server = opts[:server] 360 server_hash = server ? {:server=>server} : OPTS 361 ds = dataset 362 ds = ds.server(server) if server 363 364 raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on' 365 366 table_oid = regclass_oid(table) 367 im = input_identifier_meth 368 unless column = im.call(opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0])) 369 raise Error, "could not determine column to convert from serial to identity automatically" 370 end 371 372 column_num = ds.from(:pg_attribute). 373 where(:attrelid=>table_oid, :attname=>column). 374 get(:attnum) 375 376 pg_class = Sequel.cast('pg_class', :regclass) 377 res = ds.from(:pg_depend). 378 where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i'). 379 select_map([:objid, Sequel.as({:deptype=>'i'}, :v)]) 380 381 case res.length 382 when 0 383 raise Error, "unable to find related sequence when converting serial to identity" 384 when 1 385 seq_oid, already_identity = res.first 386 else 387 raise Error, "more than one linked sequence found when converting serial to identity" 388 end 389 390 return if already_identity 391 392 transaction(server_hash) do 393 run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash) 394 395 ds.from(:pg_depend). 396 where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a'). 397 update(:deptype=>'i') 398 399 ds.from(:pg_attribute). 400 where(:attrelid=>table_oid, :attname=>column). 401 update(:attidentity=>'d') 402 end 403 404 remove_cached_schema(table) 405 nil 406 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.
- :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 428 def create_function(name, definition, opts=OPTS) 429 self << create_function_sql(name, definition, opts) 430 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 439 def create_language(name, opts=OPTS) 440 self << create_language_sql(name, opts) 441 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 448 def create_schema(name, opts=OPTS) 449 self << create_schema_sql(name, opts) 450 end
Support partitions of tables using the :partition_of option.
# File lib/sequel/adapters/shared/postgres.rb 453 def create_table(name, options=OPTS, &block) 454 if options[:partition_of] 455 create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options) 456 return 457 end 458 459 super 460 end
Support partitions of tables using the :partition_of option.
# File lib/sequel/adapters/shared/postgres.rb 463 def create_table?(name, options=OPTS, &block) 464 if options[:partition_of] 465 create_table(name, options.merge!(:if_not_exists=>true), &block) 466 return 467 end 468 469 super 470 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.
- :when
-
A filter to use for the trigger
# File lib/sequel/adapters/shared/postgres.rb 483 def create_trigger(table, name, function, opts=OPTS) 484 self << create_trigger_sql(table, name, function, opts) 485 end
# File lib/sequel/adapters/shared/postgres.rb 487 def database_type 488 :postgres 489 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 496 def do(code, opts=OPTS) 497 language = opts[:language] 498 run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}" 499 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 507 def drop_function(name, opts=OPTS) 508 self << drop_function_sql(name, opts) 509 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 516 def drop_language(name, opts=OPTS) 517 self << drop_language_sql(name, opts) 518 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 525 def drop_schema(name, opts=OPTS) 526 self << drop_schema_sql(name, opts) 527 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 535 def drop_trigger(table, name, opts=OPTS) 536 self << drop_trigger_sql(table, name, opts) 537 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 549 def foreign_key_list(table, opts=OPTS) 550 m = output_identifier_meth 551 schema, _ = opts.fetch(:schema, schema_and_table(table)) 552 oid = regclass_oid(table) 553 reverse = opts[:reverse] 554 555 if reverse 556 ctable = Sequel[:att2] 557 cclass = Sequel[:cl2] 558 rtable = Sequel[:att] 559 rclass = Sequel[:cl] 560 else 561 ctable = Sequel[:att] 562 cclass = Sequel[:cl] 563 rtable = Sequel[:att2] 564 rclass = Sequel[:cl2] 565 end 566 567 if server_version >= 90500 568 cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])} 569 rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])} 570 else 571 range = 0...32 572 cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])} 573 rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])} 574 end 575 576 ds = metadata_dataset. 577 from{pg_constraint.as(:co)}. 578 join(Sequel[:pg_class].as(cclass), :oid=>:conrelid). 579 join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 580 join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]). 581 join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])). 582 join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]). 583 order{[co[:conname], cpos]}. 584 where{{ 585 cl[:relkind]=>'r', 586 co[:contype]=>'f', 587 cl[:oid]=>oid, 588 cpos=>rpos 589 }}. 590 select{[ 591 co[:conname].as(:name), 592 ctable[:attname].as(:column), 593 co[:confupdtype].as(:on_update), 594 co[:confdeltype].as(:on_delete), 595 cl2[:relname].as(:table), 596 rtable[:attname].as(:refcolumn), 597 SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable), 598 nsp[:nspname].as(:schema) 599 ]} 600 601 if reverse 602 ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname]) 603 end 604 605 h = {} 606 fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 607 608 ds.each do |row| 609 if reverse 610 key = [row[:schema], row[:table], row[:name]] 611 else 612 key = row[:name] 613 end 614 615 if r = h[key] 616 r[:columns] << m.call(row[:column]) 617 r[:key] << m.call(row[:refcolumn]) 618 else 619 entry = h[key] = { 620 :name=>m.call(row[:name]), 621 :columns=>[m.call(row[:column])], 622 :key=>[m.call(row[:refcolumn])], 623 :on_update=>fklod_map[row[:on_update]], 624 :on_delete=>fklod_map[row[:on_delete]], 625 :deferrable=>row[:deferrable], 626 :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]), 627 } 628 629 unless schema 630 # If not combining schema information into the :table entry 631 # include it as a separate entry. 632 entry[:schema] = m.call(row[:schema]) 633 end 634 end 635 end 636 637 h.values 638 end
# File lib/sequel/adapters/shared/postgres.rb 640 def freeze 641 server_version 642 supports_prepared_transactions? 643 @conversion_procs.freeze 644 super 645 end
Use the pg_* system tables to determine indexes on a table
# File lib/sequel/adapters/shared/postgres.rb 648 def indexes(table, opts=OPTS) 649 m = output_identifier_meth 650 oid = regclass_oid(table, opts) 651 652 if server_version >= 90500 653 order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])] 654 else 655 range = 0...32 656 order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])] 657 end 658 659 attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey]) 660 661 ds = metadata_dataset. 662 from{pg_class.as(:tab)}. 663 join(Sequel[:pg_index].as(:ind), :indrelid=>:oid). 664 join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid). 665 join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums). 666 left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]). 667 where{{ 668 indc[:relkind]=>'i', 669 ind[:indisprimary]=>false, 670 :indexprs=>nil, 671 :indisvalid=>true, 672 tab[:oid]=>oid}}. 673 order(*order). 674 select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]} 675 676 ds = ds.where(:indpred=>nil) unless opts[:include_partial] 677 ds = ds.where(:indisready=>true) if server_version >= 80300 678 ds = ds.where(:indislive=>true) if server_version >= 90300 679 680 indexes = {} 681 ds.each do |r| 682 i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]} 683 i[:columns] << m.call(r[:column]) 684 end 685 indexes 686 end
Dataset
containing all current database locks
# File lib/sequel/adapters/shared/postgres.rb 689 def locks 690 dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]} 691 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 699 def notify(channel, opts=OPTS) 700 sql = String.new 701 sql << "NOTIFY " 702 dataset.send(:identifier_append, sql, channel) 703 if payload = opts[:payload] 704 sql << ", " 705 dataset.literal_append(sql, payload.to_s) 706 end 707 execute_ddl(sql, opts) 708 end
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 720 def primary_key_sequence(table, opts=OPTS) 721 quoted_table = quote_schema_table(table) 722 Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)} 723 sql = "#{SELECT_SERIAL_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}" 724 if pks = fetch(sql).single_record 725 value = literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence])) 726 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} 727 else 728 sql = "#{SELECT_CUSTOM_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}" 729 if pks = fetch(sql).single_record 730 value = literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence]))) 731 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} 732 end 733 end 734 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 742 def refresh_view(name, opts=OPTS) 743 run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}" 744 end
Reset the primary key sequence for the given table, basing it on the maximum current value of the table's primary key.
# File lib/sequel/adapters/shared/postgres.rb 748 def reset_primary_key_sequence(table) 749 return unless seq = primary_key_sequence(table) 750 pk = SQL::Identifier.new(primary_key(table)) 751 db = self 752 s, t = schema_and_table(table) 753 table = Sequel.qualify(s, t) if s 754 755 if server_version >= 100000 756 seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq))) 757 increment_by = :seqincrement 758 min_value = :seqmin 759 else 760 seq_ds = metadata_dataset.from(LiteralString.new(seq)) 761 increment_by = :increment_by 762 min_value = :min_value 763 end 764 765 get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)} 766 end
# File lib/sequel/adapters/shared/postgres.rb 768 def rollback_prepared_transaction(transaction_id, opts=OPTS) 769 run("ROLLBACK PREPARED #{literal(transaction_id)}", opts) 770 end
PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.
# File lib/sequel/adapters/shared/postgres.rb 774 def serial_primary_key_options 775 auto_increment_key = server_version >= 100002 ? :identity : :serial 776 {:primary_key => true, auto_increment_key => true, :type=>Integer} 777 end
The version of the PostgreSQL server, used for determining capability.
# File lib/sequel/adapters/shared/postgres.rb 780 def server_version(server=nil) 781 return @server_version if @server_version 782 ds = dataset 783 ds = ds.server(server) if server 784 @server_version ||= ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value rescue 0 785 end
PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+
# File lib/sequel/adapters/shared/postgres.rb 788 def supports_create_table_if_not_exists? 789 server_version >= 90100 790 end
PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 793 def supports_deferrable_constraints? 794 server_version >= 90000 795 end
PostgreSQL supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 798 def supports_deferrable_foreign_key_constraints? 799 true 800 end
PostgreSQL supports DROP TABLE IF EXISTS
# File lib/sequel/adapters/shared/postgres.rb 803 def supports_drop_table_if_exists? 804 true 805 end
PostgreSQL supports partial indexes.
# File lib/sequel/adapters/shared/postgres.rb 808 def supports_partial_indexes? 809 true 810 end
PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.
# File lib/sequel/adapters/shared/postgres.rb 819 def supports_prepared_transactions? 820 return @supports_prepared_transactions if defined?(@supports_prepared_transactions) 821 @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0 822 end
PostgreSQL supports savepoints
# File lib/sequel/adapters/shared/postgres.rb 825 def supports_savepoints? 826 true 827 end
PostgreSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/postgres.rb 830 def supports_transaction_isolation_levels? 831 true 832 end
PostgreSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/postgres.rb 835 def supports_transactional_ddl? 836 true 837 end
PostgreSQL 9.0+ supports trigger conditions.
# File lib/sequel/adapters/shared/postgres.rb 813 def supports_trigger_conditions? 814 server_version >= 90000 815 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 848 def tables(opts=OPTS, &block) 849 pg_class_relname('r', opts, &block) 850 end
Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.
# File lib/sequel/adapters/shared/postgres.rb 854 def type_supported?(type) 855 Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)} 856 supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0 857 Sequel.synchronize{return @supported_types[type] = supported} 858 end
Creates a dataset that uses the VALUES clause:
DB.values([[1, 2], [3, 4]]) # VALUES ((1, 2), (3, 4)) DB.values([[1, 2], [3, 4]]).order(:column2).limit(1, 1) # VALUES ((1, 2), (3, 4)) ORDER BY column2 LIMIT 1 OFFSET 1
# File lib/sequel/adapters/shared/postgres.rb 867 def values(v) 868 @default_dataset.clone(:values=>v) 869 end
Array
of symbols specifying view names in the current database.
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 879 def views(opts=OPTS) 880 relkind = opts[:materialized] ? 'm' : 'v' 881 pg_class_relname(relkind, opts) 882 end
Private Instance Methods
# File lib/sequel/adapters/shared/postgres.rb 886 def alter_table_add_column_sql(table, op) 887 "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}" 888 end
# File lib/sequel/adapters/shared/postgres.rb 904 def alter_table_drop_column_sql(table, op) 905 "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}" 906 end
# File lib/sequel/adapters/shared/postgres.rb 890 def alter_table_generator_class 891 Postgres::AlterTableGenerator 892 end
# File lib/sequel/adapters/shared/postgres.rb 894 def alter_table_set_column_type_sql(table, op) 895 s = super 896 if using = op[:using] 897 using = Sequel::LiteralString.new(using) if using.is_a?(String) 898 s += ' USING ' 899 s << literal(using) 900 end 901 s 902 end
# File lib/sequel/adapters/shared/postgres.rb 908 def alter_table_validate_constraint_sql(table, op) 909 "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}" 910 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 915 def begin_new_transaction(conn, opts) 916 super 917 if opts.has_key?(:synchronous) 918 case sync = opts[:synchronous] 919 when true 920 sync = :on 921 when false 922 sync = :off 923 when nil 924 return 925 end 926 927 log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}") 928 end 929 end
Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.
# File lib/sequel/adapters/shared/postgres.rb 932 def begin_savepoint(conn, opts) 933 super 934 935 unless (read_only = opts[:read_only]).nil? 936 log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}") 937 end 938 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 942 def column_definition_collate_sql(sql, column) 943 if collate = column[:collate] 944 collate = literal(collate) unless collate.is_a?(String) 945 sql << " COLLATE #{collate}" 946 end 947 end
Support identity columns, but only use the identity SQL
syntax if no default value is given.
# File lib/sequel/adapters/shared/postgres.rb 951 def column_definition_default_sql(sql, column) 952 super 953 if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default] 954 if (identity = column[:identity]) 955 sql << " GENERATED " 956 sql << (identity == :always ? "ALWAYS" : "BY DEFAULT") 957 sql << " AS IDENTITY" 958 elsif (generated = column[:generated_always_as]) 959 sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED" 960 end 961 end 962 end
Handle PostgreSQL specific default format.
# File lib/sequel/adapters/shared/postgres.rb 965 def column_schema_normalize_default(default, type) 966 if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default) 967 default = m[1] || m[2] 968 end 969 super(default, type) 970 end
PostgreSQL can't combine rename_column operations, and it can combine the custom validate_constraint operation.
# File lib/sequel/adapters/shared/postgres.rb 984 def combinable_alter_table_op?(op) 985 (super || op[:op] == :validate_constraint) && op[:op] != :rename_column 986 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 974 def commit_transaction(conn, opts=OPTS) 975 if (s = opts[:prepare]) && savepoint_level(conn) <= 1 976 log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}") 977 else 978 super 979 end 980 end
The SQL
queries to execute when starting a new connection.
# File lib/sequel/adapters/shared/postgres.rb 990 def connection_configuration_sqls(opts=@opts) 991 sqls = [] 992 993 sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true)) 994 995 cmm = opts.fetch(:client_min_messages, :warning) 996 if cmm && !cmm.to_s.empty? 997 cmm = cmm.to_s.upcase.strip 998 unless VALID_CLIENT_MIN_MESSAGES.include?(cmm) 999 raise Error, "Unsupported client_min_messages setting: #{cmm}" 1000 end 1001 sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'" 1002 end 1003 1004 if search_path = opts[:search_path] 1005 case search_path 1006 when String 1007 search_path = search_path.split(",").map(&:strip) 1008 when Array 1009 # nil 1010 else 1011 raise Error, "unrecognized value for :search_path option: #{search_path.inspect}" 1012 end 1013 sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}" 1014 end 1015 1016 sqls 1017 end
Handle exclusion constraints.
# File lib/sequel/adapters/shared/postgres.rb 1020 def constraint_definition_sql(constraint) 1021 case constraint[:type] 1022 when :exclude 1023 elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ') 1024 sql = String.new 1025 sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}" 1026 constraint_deferrable_sql_append(sql, constraint[:deferrable]) 1027 sql 1028 when :foreign_key, :check 1029 sql = super 1030 if constraint[:not_valid] 1031 sql << " NOT VALID" 1032 end 1033 sql 1034 else 1035 super 1036 end 1037 end
SQL
for doing fast table insert from stdin.
# File lib/sequel/adapters/shared/postgres.rb 1068 def copy_into_sql(table, opts) 1069 sql = String.new 1070 sql << "COPY #{literal(table)}" 1071 if cols = opts[:columns] 1072 sql << literal(Array(cols)) 1073 end 1074 sql << " FROM STDIN" 1075 if opts[:options] || opts[:format] 1076 sql << " (" 1077 sql << "FORMAT #{opts[:format]}" if opts[:format] 1078 sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1079 sql << ')' 1080 end 1081 sql 1082 end
SQL
for doing fast table output to stdout.
# File lib/sequel/adapters/shared/postgres.rb 1085 def copy_table_sql(table, opts) 1086 if table.is_a?(String) 1087 table 1088 else 1089 if opts[:options] || opts[:format] 1090 options = String.new 1091 options << " (" 1092 options << "FORMAT #{opts[:format]}" if opts[:format] 1093 options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1094 options << ')' 1095 end 1096 table = if table.is_a?(::Sequel::Dataset) 1097 "(#{table.sql})" 1098 else 1099 literal(table) 1100 end 1101 "COPY #{table} TO STDOUT#{options}" 1102 end 1103 end
SQL
statement to create database function.
# File lib/sequel/adapters/shared/postgres.rb 1106 def create_function_sql(name, definition, opts=OPTS) 1107 args = opts[:args] 1108 if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)} 1109 returns = opts[:returns] || 'void' 1110 end 1111 language = opts[:language] || 'SQL' 1112 <<-END 1113 CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)} 1114 #{"RETURNS #{returns}" if returns} 1115 LANGUAGE #{language} 1116 #{opts[:behavior].to_s.upcase if opts[:behavior]} 1117 #{'STRICT' if opts[:strict]} 1118 #{'SECURITY DEFINER' if opts[:security_definer]} 1119 #{"COST #{opts[:cost]}" if opts[:cost]} 1120 #{"ROWS #{opts[:rows]}" if opts[:rows]} 1121 #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]} 1122 AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]} 1123 END 1124 end
SQL
for creating a procedural language.
# File lib/sequel/adapters/shared/postgres.rb 1127 def create_language_sql(name, opts=OPTS) 1128 "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]}" 1129 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 1133 def create_partition_of_table_from_generator(name, generator, options) 1134 execute_ddl(create_partition_of_table_sql(name, generator, options)) 1135 end
SQL
for creating a partition of another table.
# File lib/sequel/adapters/shared/postgres.rb 1138 def create_partition_of_table_sql(name, generator, options) 1139 sql = create_table_prefix_sql(name, options).dup 1140 1141 sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}" 1142 1143 case generator.partition_type 1144 when :range 1145 from, to = generator.range 1146 sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}" 1147 when :list 1148 sql << " FOR VALUES IN #{literal(generator.list)}" 1149 when :hash 1150 mod, remainder = generator.hash_values 1151 sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})" 1152 when :default 1153 sql << " DEFAULT" 1154 end 1155 1156 sql << create_table_suffix_sql(name, options) 1157 1158 sql 1159 end
SQL
for creating a schema.
# File lib/sequel/adapters/shared/postgres.rb 1162 def create_schema_sql(name, opts=OPTS) 1163 "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}" 1164 end
# File lib/sequel/adapters/shared/postgres.rb 1220 def create_table_as_sql(name, sql, options) 1221 result = create_table_prefix_sql name, options 1222 if on_commit = options[:on_commit] 1223 result += " ON COMMIT #{ON_COMMIT[on_commit]}" 1224 end 1225 result += " AS #{sql}" 1226 end
# File lib/sequel/adapters/shared/postgres.rb 1228 def create_table_generator_class 1229 Postgres::CreateTableGenerator 1230 end
DDL statement for creating a table with the given name, columns, and options
# File lib/sequel/adapters/shared/postgres.rb 1167 def create_table_prefix_sql(name, options) 1168 prefix_sql = if options[:temp] 1169 raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged] 1170 raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign] 1171 temporary_table_sql 1172 elsif options[:foreign] 1173 raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged] 1174 'FOREIGN ' 1175 elsif options[:unlogged] 1176 'UNLOGGED ' 1177 end 1178 1179 "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}" 1180 end
SQL
for creating a table with PostgreSQL specific options
# File lib/sequel/adapters/shared/postgres.rb 1183 def create_table_sql(name, generator, options) 1184 "#{super}#{create_table_suffix_sql(name, options)}" 1185 end
Handle various PostgreSQl specific table extensions such as inheritance, partitioning, tablespaces, and foreign tables.
# File lib/sequel/adapters/shared/postgres.rb 1189 def create_table_suffix_sql(name, options) 1190 sql = String.new 1191 1192 if inherits = options[:inherits] 1193 sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})" 1194 end 1195 1196 if partition_by = options[:partition_by] 1197 sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}" 1198 end 1199 1200 if on_commit = options[:on_commit] 1201 raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp] 1202 raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit) 1203 sql << " ON COMMIT #{ON_COMMIT[on_commit]}" 1204 end 1205 1206 if tablespace = options[:tablespace] 1207 sql << " TABLESPACE #{quote_identifier(tablespace)}" 1208 end 1209 1210 if server = options[:foreign] 1211 sql << " SERVER #{quote_identifier(server)}" 1212 if foreign_opts = options[:options] 1213 sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})" 1214 end 1215 end 1216 1217 sql 1218 end
SQL
for creating a database trigger.
# File lib/sequel/adapters/shared/postgres.rb 1233 def create_trigger_sql(table, name, function, opts=OPTS) 1234 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 1235 whence = opts[:after] ? 'AFTER' : 'BEFORE' 1236 if filter = opts[:when] 1237 raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions? 1238 filter = " WHEN #{filter_expr(filter)}" 1239 end 1240 "CREATE TRIGGER #{name} #{whence} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)}#{' FOR EACH ROW' if opts[:each_row]}#{filter} EXECUTE PROCEDURE #{function}(#{Array(opts[:args]).map{|a| literal(a)}.join(', ')})" 1241 end
DDL fragment for initial part of CREATE VIEW statement
# File lib/sequel/adapters/shared/postgres.rb 1244 def create_view_prefix_sql(name, options) 1245 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]) 1246 1247 if tablespace = options[:tablespace] 1248 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1249 end 1250 1251 sql 1252 end
# File lib/sequel/adapters/shared/postgres.rb 1063 def database_error_regexps 1064 DATABASE_ERROR_REGEXPS 1065 end
# File lib/sequel/adapters/shared/postgres.rb 1039 def database_specific_error_class_from_sqlstate(sqlstate) 1040 if sqlstate == '23P01' 1041 ExclusionConstraintViolation 1042 elsif sqlstate == '40P01' 1043 SerializationFailure 1044 elsif sqlstate == '55P03' 1045 DatabaseLockTimeout 1046 else 1047 super 1048 end 1049 end
SQL
for dropping a function from the database.
# File lib/sequel/adapters/shared/postgres.rb 1255 def drop_function_sql(name, opts=OPTS) 1256 "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}" 1257 end
Support :if_exists, :cascade, and :concurrently options.
# File lib/sequel/adapters/shared/postgres.rb 1260 def drop_index_sql(table, op) 1261 sch, _ = schema_and_table(table) 1262 "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]}" 1263 end
SQL
for dropping a procedural language from the database.
# File lib/sequel/adapters/shared/postgres.rb 1266 def drop_language_sql(name, opts=OPTS) 1267 "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}" 1268 end
SQL
for dropping a schema from the database.
# File lib/sequel/adapters/shared/postgres.rb 1271 def drop_schema_sql(name, opts=OPTS) 1272 "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}" 1273 end
Support :foreign tables
# File lib/sequel/adapters/shared/postgres.rb 1281 def drop_table_sql(name, options) 1282 "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}" 1283 end
SQL
for dropping a trigger from the database.
# File lib/sequel/adapters/shared/postgres.rb 1276 def drop_trigger_sql(table, name, opts=OPTS) 1277 "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}" 1278 end
SQL
for dropping a view from the database.
# File lib/sequel/adapters/shared/postgres.rb 1286 def drop_view_sql(name, opts=OPTS) 1287 "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}" 1288 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 1292 def filter_schema(ds, opts) 1293 expr = if schema = opts[:schema] 1294 schema.to_s 1295 else 1296 Sequel.function(:any, Sequel.function(:current_schemas, false)) 1297 end 1298 ds.where{{pg_namespace[:nspname]=>expr}} 1299 end
# File lib/sequel/adapters/shared/postgres.rb 1301 def index_definition_sql(table_name, index) 1302 cols = index[:columns] 1303 index_name = index[:name] || default_index_name(table_name, cols) 1304 expr = if o = index[:opclass] 1305 "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})" 1306 else 1307 literal(Array(cols)) 1308 end 1309 if_not_exists = " IF NOT EXISTS" if index[:if_not_exists] 1310 unique = "UNIQUE " if index[:unique] 1311 index_type = index[:type] 1312 filter = index[:where] || index[:filter] 1313 filter = " WHERE #{filter_expr(filter)}" if filter 1314 case index_type 1315 when :full_text 1316 expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))" 1317 index_type = index[:index_type] || :gin 1318 when :spatial 1319 index_type = :gist 1320 end 1321 "CREATE #{unique}INDEX#{' CONCURRENTLY' if index[:concurrently]}#{if_not_exists} #{quote_identifier(index_name)} ON #{quote_schema_table(table_name)} #{"USING #{index_type} " if index_type}#{expr}#{" INCLUDE #{literal(Array(index[:include]))}" if index[:include]}#{" TABLESPACE #{quote_identifier(index[:tablespace])}" if index[:tablespace]}#{filter}" 1322 end
Setup datastructures shared by all postgres adapters.
# File lib/sequel/adapters/shared/postgres.rb 1325 def initialize_postgres_adapter 1326 @primary_keys = {} 1327 @primary_key_sequences = {} 1328 @supported_types = {} 1329 procs = @conversion_procs = CONVERSION_PROCS.dup 1330 procs[1184] = procs[1114] = method(:to_application_timestamp) 1331 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/postgres.rb 1334 def pg_class_relname(type, opts) 1335 ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace) 1336 ds = filter_schema(ds, opts) 1337 m = output_identifier_meth 1338 if block_given? 1339 yield(ds) 1340 elsif opts[:qualify] 1341 ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)} 1342 else 1343 ds.map{|r| m.call(r[:relname])} 1344 end 1345 end
Use a dollar sign instead of question mark for the argument placeholder.
# File lib/sequel/adapters/shared/postgres.rb 1348 def prepared_arg_placeholder 1349 PREPARED_ARG_PLACEHOLDER 1350 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 1354 def regclass_oid(expr, opts=OPTS) 1355 if expr.is_a?(String) && !expr.is_a?(LiteralString) 1356 expr = Sequel.identifier(expr) 1357 end 1358 1359 sch, table = schema_and_table(expr) 1360 sch ||= opts[:schema] 1361 if sch 1362 expr = Sequel.qualify(sch, table) 1363 end 1364 1365 expr = if ds = opts[:dataset] 1366 ds.literal(expr) 1367 else 1368 literal(expr) 1369 end 1370 1371 Sequel.cast(expr.to_s,:regclass).cast(:oid) 1372 end
Remove the cached entries for primary keys and sequences when a table is changed.
# File lib/sequel/adapters/shared/postgres.rb 1375 def remove_cached_schema(table) 1376 tab = quote_schema_table(table) 1377 Sequel.synchronize do 1378 @primary_keys.delete(tab) 1379 @primary_key_sequences.delete(tab) 1380 end 1381 super 1382 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 speciying a new schema in new_name will not have an effect.
# File lib/sequel/adapters/shared/postgres.rb 1386 def rename_table_sql(name, new_name) 1387 "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}" 1388 end
# File lib/sequel/adapters/shared/postgres.rb 1390 def schema_column_type(db_type) 1391 case db_type 1392 when /\Ainterval\z/io 1393 :interval 1394 when /\Acitext\z/io 1395 :string 1396 else 1397 super 1398 end 1399 end
The dataset used for parsing table schemas, using the pg_* system catalogs.
# File lib/sequel/adapters/shared/postgres.rb 1402 def schema_parse_table(table_name, opts) 1403 m = output_identifier_meth(opts[:dataset]) 1404 oid = regclass_oid(table_name, opts) 1405 ds = metadata_dataset.select{[ 1406 pg_attribute[:attname].as(:name), 1407 SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid), 1408 SQL::Cast.new(basetype[:oid], :integer).as(:base_oid), 1409 SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type), 1410 SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type), 1411 SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default), 1412 SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null), 1413 SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key)]}. 1414 from(:pg_class). 1415 join(:pg_attribute, :attrelid=>:oid). 1416 join(:pg_type, :oid=>:atttypid). 1417 left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype). 1418 left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]). 1419 left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true). 1420 where{{pg_attribute[:attisdropped]=>false}}. 1421 where{pg_attribute[:attnum] > 0}. 1422 where{{pg_class[:oid]=>oid}}. 1423 order{pg_attribute[:attnum]} 1424 1425 if server_version > 100000 1426 ds = ds.select_append{pg_attribute[:attidentity]} 1427 1428 if server_version > 120000 1429 ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)} 1430 end 1431 end 1432 1433 ds.map do |row| 1434 row[:default] = nil if blank_object?(row[:default]) 1435 if row[:base_oid] 1436 row[:domain_oid] = row[:oid] 1437 row[:oid] = row.delete(:base_oid) 1438 row[:db_domain_type] = row[:db_type] 1439 row[:db_type] = row.delete(:db_base_type) 1440 else 1441 row.delete(:base_oid) 1442 row.delete(:db_base_type) 1443 end 1444 row[:type] = schema_column_type(row[:db_type]) 1445 identity = row.delete(:attidentity) 1446 if row[:primary_key] 1447 row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd' 1448 end 1449 [m.call(row.delete(:name)), row] 1450 end 1451 end
Set the transaction isolation level on the given connection
# File lib/sequel/adapters/shared/postgres.rb 1454 def set_transaction_isolation(conn, opts) 1455 level = opts.fetch(:isolation, transaction_isolation_level) 1456 read_only = opts[:read_only] 1457 deferrable = opts[:deferrable] 1458 if level || !read_only.nil? || !deferrable.nil? 1459 sql = String.new 1460 sql << "SET TRANSACTION" 1461 sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level 1462 sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil? 1463 sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil? 1464 log_connection_execute(conn, sql) 1465 end 1466 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 1469 def sql_function_args(args) 1470 "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})" 1471 end
PostgreSQL can combine multiple alter table ops into a single query.
# File lib/sequel/adapters/shared/postgres.rb 1474 def supports_combining_alter_table_ops? 1475 true 1476 end
PostgreSQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/postgres.rb 1479 def supports_create_or_replace_view? 1480 true 1481 end
Handle bigserial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1484 def type_literal_generic_bignum_symbol(column) 1485 column[:serial] ? :bigserial : super 1486 end
PostgreSQL uses the bytea data type for blobs
# File lib/sequel/adapters/shared/postgres.rb 1489 def type_literal_generic_file(column) 1490 :bytea 1491 end
Handle serial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1494 def type_literal_generic_integer(column) 1495 column[:serial] ? :serial : super 1496 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 1502 def type_literal_generic_string(column) 1503 if column[:fixed] 1504 "char(#{column[:size]||255})" 1505 elsif column[:text] == false or column[:size] 1506 "varchar(#{column[:size]||255})" 1507 else 1508 :text 1509 end 1510 end
PostgreSQL 9.4+ supports views with check option.
# File lib/sequel/adapters/shared/postgres.rb 1513 def view_with_check_option_support 1514 :local if server_version >= 90400 1515 end