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