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