# File lib/sequel/adapters/shared/postgres.rb 701 def primary_key(table, opts=OPTS) 702 quoted_table = quote_schema_table(table) 703 Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)} 704 sql = "#{SELECT_PK_SQL} AND pg_class.oid = #{literal(regclass_oid(table, opts))}" 705 value = fetch(sql).single_value 706 Sequel.synchronize{@primary_keys[quoted_table] = value} 707 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
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 473 def create_trigger(table, name, function, opts=OPTS) 474 self << create_trigger_sql(table, name, function, opts) 475 end
# File lib/sequel/adapters/shared/postgres.rb 477 def database_type 478 :postgres 479 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 486 def do(code, opts=OPTS) 487 language = opts[:language] 488 run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}" 489 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 497 def drop_function(name, opts=OPTS) 498 self << drop_function_sql(name, opts) 499 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 506 def drop_language(name, opts=OPTS) 507 self << drop_language_sql(name, opts) 508 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 515 def drop_schema(name, opts=OPTS) 516 self << drop_schema_sql(name, opts) 517 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 525 def drop_trigger(table, name, opts=OPTS) 526 self << drop_trigger_sql(table, name, opts) 527 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 539 def foreign_key_list(table, opts=OPTS) 540 m = output_identifier_meth 541 schema, _ = opts.fetch(:schema, schema_and_table(table)) 542 oid = regclass_oid(table) 543 reverse = opts[:reverse] 544 545 if reverse 546 ctable = Sequel[:att2] 547 cclass = Sequel[:cl2] 548 rtable = Sequel[:att] 549 rclass = Sequel[:cl] 550 else 551 ctable = Sequel[:att] 552 cclass = Sequel[:cl] 553 rtable = Sequel[:att2] 554 rclass = Sequel[:cl2] 555 end 556 557 if server_version >= 90500 558 cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])} 559 rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])} 560 else 561 range = 0...32 562 cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])} 563 rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])} 564 end 565 566 ds = metadata_dataset. 567 from{pg_constraint.as(:co)}. 568 join(Sequel[:pg_class].as(cclass), :oid=>:conrelid). 569 join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 570 join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]). 571 join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])). 572 join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]). 573 order{[co[:conname], cpos]}. 574 where{{ 575 cl[:relkind]=>'r', 576 co[:contype]=>'f', 577 cl[:oid]=>oid, 578 cpos=>rpos 579 }}. 580 select{[ 581 co[:conname].as(:name), 582 ctable[:attname].as(:column), 583 co[:confupdtype].as(:on_update), 584 co[:confdeltype].as(:on_delete), 585 cl2[:relname].as(:table), 586 rtable[:attname].as(:refcolumn), 587 SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable), 588 nsp[:nspname].as(:schema) 589 ]} 590 591 if reverse 592 ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname]) 593 end 594 595 h = {} 596 fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 597 598 ds.each do |row| 599 if reverse 600 key = [row[:schema], row[:table], row[:name]] 601 else 602 key = row[:name] 603 end 604 605 if r = h[key] 606 r[:columns] << m.call(row[:column]) 607 r[:key] << m.call(row[:refcolumn]) 608 else 609 entry = h[key] = { 610 :name=>m.call(row[:name]), 611 :columns=>[m.call(row[:column])], 612 :key=>[m.call(row[:refcolumn])], 613 :on_update=>fklod_map[row[:on_update]], 614 :on_delete=>fklod_map[row[:on_delete]], 615 :deferrable=>row[:deferrable], 616 :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]), 617 } 618 619 unless schema 620 # If not combining schema information into the :table entry 621 # include it as a separate entry. 622 entry[:schema] = m.call(row[:schema]) 623 end 624 end 625 end 626 627 h.values 628 end
# File lib/sequel/adapters/shared/postgres.rb 630 def freeze 631 server_version 632 supports_prepared_transactions? 633 @conversion_procs.freeze 634 super 635 end
Use the pg_* system tables to determine indexes on a table
# File lib/sequel/adapters/shared/postgres.rb 638 def indexes(table, opts=OPTS) 639 m = output_identifier_meth 640 oid = regclass_oid(table, opts) 641 642 if server_version >= 90500 643 order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])] 644 else 645 range = 0...32 646 order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])] 647 end 648 649 attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey]) 650 651 ds = metadata_dataset. 652 from{pg_class.as(:tab)}. 653 join(Sequel[:pg_index].as(:ind), :indrelid=>:oid). 654 join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid). 655 join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums). 656 left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]). 657 where{{ 658 indc[:relkind]=>'i', 659 ind[:indisprimary]=>false, 660 :indexprs=>nil, 661 :indisvalid=>true, 662 tab[:oid]=>oid}}. 663 order(*order). 664 select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]} 665 666 ds = ds.where(:indpred=>nil) unless opts[:include_partial] 667 ds = ds.where(:indisready=>true) if server_version >= 80300 668 ds = ds.where(:indislive=>true) if server_version >= 90300 669 670 indexes = {} 671 ds.each do |r| 672 i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]} 673 i[:columns] << m.call(r[:column]) 674 end 675 indexes 676 end
Dataset
containing all current database locks
# File lib/sequel/adapters/shared/postgres.rb 679 def locks 680 dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]} 681 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 689 def notify(channel, opts=OPTS) 690 sql = String.new 691 sql << "NOTIFY " 692 dataset.send(:identifier_append, sql, channel) 693 if payload = opts[:payload] 694 sql << ", " 695 dataset.literal_append(sql, payload.to_s) 696 end 697 execute_ddl(sql, opts) 698 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 710 def primary_key_sequence(table, opts=OPTS) 711 quoted_table = quote_schema_table(table) 712 Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)} 713 sql = "#{SELECT_SERIAL_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}" 714 if pks = fetch(sql).single_record 715 value = literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence])) 716 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} 717 else 718 sql = "#{SELECT_CUSTOM_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}" 719 if pks = fetch(sql).single_record 720 value = literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence]))) 721 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} 722 end 723 end 724 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 732 def refresh_view(name, opts=OPTS) 733 run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}" 734 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 738 def reset_primary_key_sequence(table) 739 return unless seq = primary_key_sequence(table) 740 pk = SQL::Identifier.new(primary_key(table)) 741 db = self 742 s, t = schema_and_table(table) 743 table = Sequel.qualify(s, t) if s 744 745 if server_version >= 100000 746 seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq))) 747 increment_by = :seqincrement 748 min_value = :seqmin 749 else 750 seq_ds = metadata_dataset.from(LiteralString.new(seq)) 751 increment_by = :increment_by 752 min_value = :min_value 753 end 754 755 get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)} 756 end
# File lib/sequel/adapters/shared/postgres.rb 758 def rollback_prepared_transaction(transaction_id, opts=OPTS) 759 run("ROLLBACK PREPARED #{literal(transaction_id)}", opts) 760 end
PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.
# File lib/sequel/adapters/shared/postgres.rb 764 def serial_primary_key_options 765 auto_increment_key = server_version >= 100002 ? :identity : :serial 766 {:primary_key => true, auto_increment_key => true, :type=>Integer} 767 end
The version of the PostgreSQL server, used for determining capability.
# File lib/sequel/adapters/shared/postgres.rb 770 def server_version(server=nil) 771 return @server_version if @server_version 772 ds = dataset 773 ds = ds.server(server) if server 774 @server_version ||= ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value rescue 0 775 end
PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+
# File lib/sequel/adapters/shared/postgres.rb 778 def supports_create_table_if_not_exists? 779 server_version >= 90100 780 end
PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 783 def supports_deferrable_constraints? 784 server_version >= 90000 785 end
PostgreSQL supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 788 def supports_deferrable_foreign_key_constraints? 789 true 790 end
PostgreSQL supports DROP TABLE IF EXISTS
# File lib/sequel/adapters/shared/postgres.rb 793 def supports_drop_table_if_exists? 794 true 795 end
PostgreSQL supports partial indexes.
# File lib/sequel/adapters/shared/postgres.rb 798 def supports_partial_indexes? 799 true 800 end
PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.
# File lib/sequel/adapters/shared/postgres.rb 809 def supports_prepared_transactions? 810 return @supports_prepared_transactions if defined?(@supports_prepared_transactions) 811 @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0 812 end
PostgreSQL supports savepoints
# File lib/sequel/adapters/shared/postgres.rb 815 def supports_savepoints? 816 true 817 end
PostgreSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/postgres.rb 820 def supports_transaction_isolation_levels? 821 true 822 end
PostgreSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/postgres.rb 825 def supports_transactional_ddl? 826 true 827 end
PostgreSQL 9.0+ supports trigger conditions.
# File lib/sequel/adapters/shared/postgres.rb 803 def supports_trigger_conditions? 804 server_version >= 90000 805 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 838 def tables(opts=OPTS, &block) 839 pg_class_relname('r', opts, &block) 840 end
Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.
# File lib/sequel/adapters/shared/postgres.rb 844 def type_supported?(type) 845 Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)} 846 supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0 847 Sequel.synchronize{return @supported_types[type] = supported} 848 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 857 def values(v) 858 @default_dataset.clone(:values=>v) 859 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 869 def views(opts=OPTS) 870 relkind = opts[:materialized] ? 'm' : 'v' 871 pg_class_relname(relkind, opts) 872 end
Private Instance Methods
# File lib/sequel/adapters/shared/postgres.rb 876 def alter_table_add_column_sql(table, op) 877 "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}" 878 end
# File lib/sequel/adapters/shared/postgres.rb 894 def alter_table_drop_column_sql(table, op) 895 "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}" 896 end
# File lib/sequel/adapters/shared/postgres.rb 880 def alter_table_generator_class 881 Postgres::AlterTableGenerator 882 end
# File lib/sequel/adapters/shared/postgres.rb 884 def alter_table_set_column_type_sql(table, op) 885 s = super 886 if using = op[:using] 887 using = Sequel::LiteralString.new(using) if using.is_a?(String) 888 s += ' USING ' 889 s << literal(using) 890 end 891 s 892 end
# File lib/sequel/adapters/shared/postgres.rb 898 def alter_table_validate_constraint_sql(table, op) 899 "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}" 900 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 905 def begin_new_transaction(conn, opts) 906 super 907 if opts.has_key?(:synchronous) 908 case sync = opts[:synchronous] 909 when true 910 sync = :on 911 when false 912 sync = :off 913 when nil 914 return 915 end 916 917 log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}") 918 end 919 end
Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.
# File lib/sequel/adapters/shared/postgres.rb 922 def begin_savepoint(conn, opts) 923 super 924 925 unless (read_only = opts[:read_only]).nil? 926 log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}") 927 end 928 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 932 def column_definition_collate_sql(sql, column) 933 if collate = column[:collate] 934 collate = literal(collate) unless collate.is_a?(String) 935 sql << " COLLATE #{collate}" 936 end 937 end
Support identity columns, but only use the identity SQL
syntax if no default value is given.
# File lib/sequel/adapters/shared/postgres.rb 941 def column_definition_default_sql(sql, column) 942 super 943 if !column[:serial] && !['serial', 'bigserial'].include?(column[:type].to_s) && !column[:default] 944 if (identity = column[:identity]) 945 sql << " GENERATED " 946 sql << (identity == :always ? "ALWAYS" : "BY DEFAULT") 947 sql << " AS IDENTITY" 948 elsif (generated = column[:generated_always_as]) 949 sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED" 950 end 951 end 952 end
Handle PostgreSQL specific default format.
# File lib/sequel/adapters/shared/postgres.rb 955 def column_schema_normalize_default(default, type) 956 if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default) 957 default = m[1] || m[2] 958 end 959 super(default, type) 960 end
PostgreSQL can't combine rename_column operations, and it can combine the custom validate_constraint operation.
# File lib/sequel/adapters/shared/postgres.rb 974 def combinable_alter_table_op?(op) 975 (super || op[:op] == :validate_constraint) && op[:op] != :rename_column 976 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 964 def commit_transaction(conn, opts=OPTS) 965 if (s = opts[:prepare]) && savepoint_level(conn) <= 1 966 log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}") 967 else 968 super 969 end 970 end
The SQL
queries to execute when starting a new connection.
# File lib/sequel/adapters/shared/postgres.rb 980 def connection_configuration_sqls(opts=@opts) 981 sqls = [] 982 983 sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true)) 984 985 cmm = opts.fetch(:client_min_messages, :warning) 986 if cmm && !cmm.to_s.empty? 987 cmm = cmm.to_s.upcase.strip 988 unless VALID_CLIENT_MIN_MESSAGES.include?(cmm) 989 raise Error, "Unsupported client_min_messages setting: #{cmm}" 990 end 991 sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'" 992 end 993 994 if search_path = opts[:search_path] 995 case search_path 996 when String 997 search_path = search_path.split(",").map(&:strip) 998 when Array 999 # nil 1000 else 1001 raise Error, "unrecognized value for :search_path option: #{search_path.inspect}" 1002 end 1003 sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}" 1004 end 1005 1006 sqls 1007 end
Handle exclusion constraints.
# File lib/sequel/adapters/shared/postgres.rb 1010 def constraint_definition_sql(constraint) 1011 case constraint[:type] 1012 when :exclude 1013 elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ') 1014 sql = String.new 1015 sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}" 1016 constraint_deferrable_sql_append(sql, constraint[:deferrable]) 1017 sql 1018 when :foreign_key, :check 1019 sql = super 1020 if constraint[:not_valid] 1021 sql << " NOT VALID" 1022 end 1023 sql 1024 else 1025 super 1026 end 1027 end
SQL
for doing fast table insert from stdin.
# File lib/sequel/adapters/shared/postgres.rb 1058 def copy_into_sql(table, opts) 1059 sql = String.new 1060 sql << "COPY #{literal(table)}" 1061 if cols = opts[:columns] 1062 sql << literal(Array(cols)) 1063 end 1064 sql << " FROM STDIN" 1065 if opts[:options] || opts[:format] 1066 sql << " (" 1067 sql << "FORMAT #{opts[:format]}" if opts[:format] 1068 sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1069 sql << ')' 1070 end 1071 sql 1072 end
SQL
for doing fast table output to stdout.
# File lib/sequel/adapters/shared/postgres.rb 1075 def copy_table_sql(table, opts) 1076 if table.is_a?(String) 1077 table 1078 else 1079 if opts[:options] || opts[:format] 1080 options = String.new 1081 options << " (" 1082 options << "FORMAT #{opts[:format]}" if opts[:format] 1083 options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 1084 options << ')' 1085 end 1086 table = if table.is_a?(::Sequel::Dataset) 1087 "(#{table.sql})" 1088 else 1089 literal(table) 1090 end 1091 "COPY #{table} TO STDOUT#{options}" 1092 end 1093 end
SQL
statement to create database function.
# File lib/sequel/adapters/shared/postgres.rb 1096 def create_function_sql(name, definition, opts=OPTS) 1097 args = opts[:args] 1098 if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)} 1099 returns = opts[:returns] || 'void' 1100 end 1101 language = opts[:language] || 'SQL' 1102 <<-END 1103 CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)} 1104 #{"RETURNS #{returns}" if returns} 1105 LANGUAGE #{language} 1106 #{opts[:behavior].to_s.upcase if opts[:behavior]} 1107 #{'STRICT' if opts[:strict]} 1108 #{'SECURITY DEFINER' if opts[:security_definer]} 1109 #{"COST #{opts[:cost]}" if opts[:cost]} 1110 #{"ROWS #{opts[:rows]}" if opts[:rows]} 1111 #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]} 1112 AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]} 1113 END 1114 end
SQL
for creating a procedural language.
# File lib/sequel/adapters/shared/postgres.rb 1117 def create_language_sql(name, opts=OPTS) 1118 "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]}" 1119 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 1123 def create_partition_of_table_from_generator(name, generator, options) 1124 execute_ddl(create_partition_of_table_sql(name, generator, options)) 1125 end
SQL
for creating a partition of another table.
# File lib/sequel/adapters/shared/postgres.rb 1128 def create_partition_of_table_sql(name, generator, options) 1129 sql = create_table_prefix_sql(name, options).dup 1130 1131 sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}" 1132 1133 case generator.partition_type 1134 when :range 1135 from, to = generator.range 1136 sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}" 1137 when :list 1138 sql << " FOR VALUES IN #{literal(generator.list)}" 1139 when :hash 1140 mod, remainder = generator.hash_values 1141 sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})" 1142 when :default 1143 sql << " DEFAULT" 1144 end 1145 1146 sql << create_table_suffix_sql(name, options) 1147 1148 sql 1149 end
SQL
for creating a schema.
# File lib/sequel/adapters/shared/postgres.rb 1152 def create_schema_sql(name, opts=OPTS) 1153 "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}" 1154 end
# File lib/sequel/adapters/shared/postgres.rb 1210 def create_table_as_sql(name, sql, options) 1211 result = create_table_prefix_sql name, options 1212 if on_commit = options[:on_commit] 1213 result += " ON COMMIT #{ON_COMMIT[on_commit]}" 1214 end 1215 result += " AS #{sql}" 1216 end
# File lib/sequel/adapters/shared/postgres.rb 1218 def create_table_generator_class 1219 Postgres::CreateTableGenerator 1220 end
DDL statement for creating a table with the given name, columns, and options
# File lib/sequel/adapters/shared/postgres.rb 1157 def create_table_prefix_sql(name, options) 1158 prefix_sql = if options[:temp] 1159 raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged] 1160 raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign] 1161 temporary_table_sql 1162 elsif options[:foreign] 1163 raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged] 1164 'FOREIGN ' 1165 elsif options[:unlogged] 1166 'UNLOGGED ' 1167 end 1168 1169 "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}" 1170 end
SQL
for creating a table with PostgreSQL specific options
# File lib/sequel/adapters/shared/postgres.rb 1173 def create_table_sql(name, generator, options) 1174 "#{super}#{create_table_suffix_sql(name, options)}" 1175 end
Handle various PostgreSQl specific table extensions such as inheritance, partitioning, tablespaces, and foreign tables.
# File lib/sequel/adapters/shared/postgres.rb 1179 def create_table_suffix_sql(name, options) 1180 sql = String.new 1181 1182 if inherits = options[:inherits] 1183 sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})" 1184 end 1185 1186 if partition_by = options[:partition_by] 1187 sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}" 1188 end 1189 1190 if on_commit = options[:on_commit] 1191 raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp] 1192 raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit) 1193 sql << " ON COMMIT #{ON_COMMIT[on_commit]}" 1194 end 1195 1196 if tablespace = options[:tablespace] 1197 sql << " TABLESPACE #{quote_identifier(tablespace)}" 1198 end 1199 1200 if server = options[:foreign] 1201 sql << " SERVER #{quote_identifier(server)}" 1202 if foreign_opts = options[:options] 1203 sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})" 1204 end 1205 end 1206 1207 sql 1208 end
SQL
for creating a database trigger.
# File lib/sequel/adapters/shared/postgres.rb 1223 def create_trigger_sql(table, name, function, opts=OPTS) 1224 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 1225 whence = opts[:after] ? 'AFTER' : 'BEFORE' 1226 if filter = opts[:when] 1227 raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions? 1228 filter = " WHEN #{filter_expr(filter)}" 1229 end 1230 "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(', ')})" 1231 end
DDL fragment for initial part of CREATE VIEW statement
# File lib/sequel/adapters/shared/postgres.rb 1234 def create_view_prefix_sql(name, options) 1235 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]) 1236 1237 if tablespace = options[:tablespace] 1238 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1239 end 1240 1241 sql 1242 end
# File lib/sequel/adapters/shared/postgres.rb 1053 def database_error_regexps 1054 DATABASE_ERROR_REGEXPS 1055 end
# File lib/sequel/adapters/shared/postgres.rb 1029 def database_specific_error_class_from_sqlstate(sqlstate) 1030 if sqlstate == '23P01' 1031 ExclusionConstraintViolation 1032 elsif sqlstate == '40P01' 1033 SerializationFailure 1034 elsif sqlstate == '55P03' 1035 DatabaseLockTimeout 1036 else 1037 super 1038 end 1039 end
SQL
for dropping a function from the database.
# File lib/sequel/adapters/shared/postgres.rb 1245 def drop_function_sql(name, opts=OPTS) 1246 "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}" 1247 end
Support :if_exists, :cascade, and :concurrently options.
# File lib/sequel/adapters/shared/postgres.rb 1250 def drop_index_sql(table, op) 1251 sch, _ = schema_and_table(table) 1252 "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]}" 1253 end
SQL
for dropping a procedural language from the database.
# File lib/sequel/adapters/shared/postgres.rb 1256 def drop_language_sql(name, opts=OPTS) 1257 "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}" 1258 end
SQL
for dropping a schema from the database.
# File lib/sequel/adapters/shared/postgres.rb 1261 def drop_schema_sql(name, opts=OPTS) 1262 "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}" 1263 end
Support :foreign tables
# File lib/sequel/adapters/shared/postgres.rb 1271 def drop_table_sql(name, options) 1272 "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}" 1273 end
SQL
for dropping a trigger from the database.
# File lib/sequel/adapters/shared/postgres.rb 1266 def drop_trigger_sql(table, name, opts=OPTS) 1267 "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}" 1268 end
SQL
for dropping a view from the database.
# File lib/sequel/adapters/shared/postgres.rb 1276 def drop_view_sql(name, opts=OPTS) 1277 "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}" 1278 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 1282 def filter_schema(ds, opts) 1283 expr = if schema = opts[:schema] 1284 schema.to_s 1285 else 1286 Sequel.function(:any, Sequel.function(:current_schemas, false)) 1287 end 1288 ds.where{{pg_namespace[:nspname]=>expr}} 1289 end
# File lib/sequel/adapters/shared/postgres.rb 1291 def index_definition_sql(table_name, index) 1292 cols = index[:columns] 1293 index_name = index[:name] || default_index_name(table_name, cols) 1294 expr = if o = index[:opclass] 1295 "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})" 1296 else 1297 literal(Array(cols)) 1298 end 1299 if_not_exists = " IF NOT EXISTS" if index[:if_not_exists] 1300 unique = "UNIQUE " if index[:unique] 1301 index_type = index[:type] 1302 filter = index[:where] || index[:filter] 1303 filter = " WHERE #{filter_expr(filter)}" if filter 1304 case index_type 1305 when :full_text 1306 expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))" 1307 index_type = index[:index_type] || :gin 1308 when :spatial 1309 index_type = :gist 1310 end 1311 "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}" 1312 end
Setup datastructures shared by all postgres adapters.
# File lib/sequel/adapters/shared/postgres.rb 1315 def initialize_postgres_adapter 1316 @primary_keys = {} 1317 @primary_key_sequences = {} 1318 @supported_types = {} 1319 procs = @conversion_procs = CONVERSION_PROCS.dup 1320 procs[1184] = procs[1114] = method(:to_application_timestamp) 1321 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/postgres.rb 1324 def pg_class_relname(type, opts) 1325 ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace) 1326 ds = filter_schema(ds, opts) 1327 m = output_identifier_meth 1328 if block_given? 1329 yield(ds) 1330 elsif opts[:qualify] 1331 ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)} 1332 else 1333 ds.map{|r| m.call(r[:relname])} 1334 end 1335 end
Use a dollar sign instead of question mark for the argument placeholder.
# File lib/sequel/adapters/shared/postgres.rb 1338 def prepared_arg_placeholder 1339 PREPARED_ARG_PLACEHOLDER 1340 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 1344 def regclass_oid(expr, opts=OPTS) 1345 if expr.is_a?(String) && !expr.is_a?(LiteralString) 1346 expr = Sequel.identifier(expr) 1347 end 1348 1349 sch, table = schema_and_table(expr) 1350 sch ||= opts[:schema] 1351 if sch 1352 expr = Sequel.qualify(sch, table) 1353 end 1354 1355 expr = if ds = opts[:dataset] 1356 ds.literal(expr) 1357 else 1358 literal(expr) 1359 end 1360 1361 Sequel.cast(expr.to_s,:regclass).cast(:oid) 1362 end
Remove the cached entries for primary keys and sequences when a table is changed.
# File lib/sequel/adapters/shared/postgres.rb 1365 def remove_cached_schema(table) 1366 tab = quote_schema_table(table) 1367 Sequel.synchronize do 1368 @primary_keys.delete(tab) 1369 @primary_key_sequences.delete(tab) 1370 end 1371 super 1372 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 1376 def rename_table_sql(name, new_name) 1377 "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}" 1378 end
# File lib/sequel/adapters/shared/postgres.rb 1380 def schema_column_type(db_type) 1381 case db_type 1382 when /\Ainterval\z/io 1383 :interval 1384 when /\Acitext\z/io 1385 :string 1386 else 1387 super 1388 end 1389 end
The dataset used for parsing table schemas, using the pg_* system catalogs.
# File lib/sequel/adapters/shared/postgres.rb 1392 def schema_parse_table(table_name, opts) 1393 m = output_identifier_meth(opts[:dataset]) 1394 oid = regclass_oid(table_name, opts) 1395 ds = metadata_dataset.select{[ 1396 pg_attribute[:attname].as(:name), 1397 SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid), 1398 SQL::Cast.new(basetype[:oid], :integer).as(:base_oid), 1399 SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type), 1400 SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type), 1401 SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default), 1402 SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null), 1403 SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key)]}. 1404 from(:pg_class). 1405 join(:pg_attribute, :attrelid=>:oid). 1406 join(:pg_type, :oid=>:atttypid). 1407 left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype). 1408 left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]). 1409 left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true). 1410 where{{pg_attribute[:attisdropped]=>false}}. 1411 where{pg_attribute[:attnum] > 0}. 1412 where{{pg_class[:oid]=>oid}}. 1413 order{pg_attribute[:attnum]} 1414 1415 if server_version > 100000 1416 ds = ds.select_append{pg_attribute[:attidentity]} 1417 1418 if server_version > 120000 1419 ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)} 1420 end 1421 end 1422 1423 ds.map do |row| 1424 row[:default] = nil if blank_object?(row[:default]) 1425 if row[:base_oid] 1426 row[:domain_oid] = row[:oid] 1427 row[:oid] = row.delete(:base_oid) 1428 row[:db_domain_type] = row[:db_type] 1429 row[:db_type] = row.delete(:db_base_type) 1430 else 1431 row.delete(:base_oid) 1432 row.delete(:db_base_type) 1433 end 1434 row[:type] = schema_column_type(row[:db_type]) 1435 identity = row.delete(:attidentity) 1436 if row[:primary_key] 1437 row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd' 1438 end 1439 [m.call(row.delete(:name)), row] 1440 end 1441 end
Set the transaction isolation level on the given connection
# File lib/sequel/adapters/shared/postgres.rb 1444 def set_transaction_isolation(conn, opts) 1445 level = opts.fetch(:isolation, transaction_isolation_level) 1446 read_only = opts[:read_only] 1447 deferrable = opts[:deferrable] 1448 if level || !read_only.nil? || !deferrable.nil? 1449 sql = String.new 1450 sql << "SET TRANSACTION" 1451 sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level 1452 sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil? 1453 sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil? 1454 log_connection_execute(conn, sql) 1455 end 1456 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 1459 def sql_function_args(args) 1460 "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})" 1461 end
PostgreSQL can combine multiple alter table ops into a single query.
# File lib/sequel/adapters/shared/postgres.rb 1464 def supports_combining_alter_table_ops? 1465 true 1466 end
PostgreSQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/postgres.rb 1469 def supports_create_or_replace_view? 1470 true 1471 end
Handle bigserial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1474 def type_literal_generic_bignum_symbol(column) 1475 column[:serial] ? :bigserial : super 1476 end
PostgreSQL uses the bytea data type for blobs
# File lib/sequel/adapters/shared/postgres.rb 1479 def type_literal_generic_file(column) 1480 :bytea 1481 end
Handle serial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1484 def type_literal_generic_integer(column) 1485 column[:serial] ? :serial : super 1486 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 1492 def type_literal_generic_string(column) 1493 if column[:fixed] 1494 "char(#{column[:size]||255})" 1495 elsif column[:text] == false or column[:size] 1496 "varchar(#{column[:size]||255})" 1497 else 1498 :text 1499 end 1500 end
PostgreSQL 9.4+ supports views with check option.
# File lib/sequel/adapters/shared/postgres.rb 1503 def view_with_check_option_support 1504 :local if server_version >= 90400 1505 end