# File lib/sequel/adapters/shared/postgres.rb 601 def primary_key(table, opts=OPTS) 602 quoted_table = quote_schema_table(table) 603 Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)} 604 sql = "#{SELECT_PK_SQL} AND pg_class.oid = #{literal(regclass_oid(table, opts))}" 605 value = fetch(sql).single_value 606 Sequel.synchronize{@primary_keys[quoted_table] = value} 607 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 206 def add_conversion_proc(oid, callable=nil, &block) 207 conversion_procs[oid] = callable || block 208 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 213 def add_named_conversion_proc(name, &block) 214 unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid) 215 raise Error, "No matching type in pg_type for #{name.inspect}" 216 end 217 add_conversion_proc(oid, block) 218 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 229 def check_constraints(table) 230 m = output_identifier_meth 231 232 rows = metadata_dataset. 233 from{pg_constraint.as(:co)}. 234 left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 235 where(:conrelid=>regclass_oid(table), :contype=>'c'). 236 select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]} 237 238 hash = {} 239 rows.each do |row| 240 constraint = m.call(row[:constraint]) 241 entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]} 242 entry[:columns] << m.call(row[:column]) if row[:column] 243 end 244 245 hash 246 end
# File lib/sequel/adapters/shared/postgres.rb 220 def commit_prepared_transaction(transaction_id, opts=OPTS) 221 run("COMMIT PREPARED #{literal(transaction_id)}", opts) 222 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 266 def convert_serial_to_identity(table, opts=OPTS) 267 raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002 268 269 server = opts[:server] 270 server_hash = server ? {:server=>server} : OPTS 271 ds = dataset 272 ds = ds.server(server) if server 273 274 raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on' 275 276 table_oid = regclass_oid(table) 277 im = input_identifier_meth 278 unless column = im.call(opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0])) 279 raise Error, "could not determine column to convert from serial to identity automatically" 280 end 281 282 column_num = ds.from(:pg_attribute). 283 where(:attrelid=>table_oid, :attname=>column). 284 get(:attnum) 285 286 pg_class = Sequel.cast('pg_class', :regclass) 287 res = ds.from(:pg_depend). 288 where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i'). 289 select_map([:objid, Sequel.as({:deptype=>'i'}, :v)]) 290 291 case res.length 292 when 0 293 raise Error, "unable to find related sequence when converting serial to identity" 294 when 1 295 seq_oid, already_identity = res.first 296 else 297 raise Error, "more than one linked sequence found when converting serial to identity" 298 end 299 300 return if already_identity 301 302 transaction(server_hash) do 303 run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash) 304 305 ds.from(:pg_depend). 306 where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a'). 307 update(:deptype=>'i') 308 309 ds.from(:pg_attribute). 310 where(:attrelid=>table_oid, :attname=>column). 311 update(:attidentity=>'d') 312 end 313 314 remove_cached_schema(table) 315 nil 316 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 338 def create_function(name, definition, opts=OPTS) 339 self << create_function_sql(name, definition, opts) 340 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 349 def create_language(name, opts=OPTS) 350 self << create_language_sql(name, opts) 351 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 358 def create_schema(name, opts=OPTS) 359 self << create_schema_sql(name, opts) 360 end
Create a trigger in the database. Arguments:
- table
-
the table on which this trigger operates
- name
-
the name of this trigger
- function
-
the function to call for this trigger, which should return type trigger.
- opts
-
options hash:
- :after
-
Calls the trigger after execution instead of before.
- :args
-
An argument or array of arguments to pass to the function.
- :each_row
-
Calls the trigger for each row instead of for each statement.
- :events
-
Can be :insert, :update, :delete, or an array of any of those. Calls the trigger whenever that type of statement is used. By default, the trigger is called for insert, update, or delete.
- :when
-
A filter to use for the trigger
# File lib/sequel/adapters/shared/postgres.rb 373 def create_trigger(table, name, function, opts=OPTS) 374 self << create_trigger_sql(table, name, function, opts) 375 end
# File lib/sequel/adapters/shared/postgres.rb 377 def database_type 378 :postgres 379 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 386 def do(code, opts=OPTS) 387 language = opts[:language] 388 run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}" 389 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 397 def drop_function(name, opts=OPTS) 398 self << drop_function_sql(name, opts) 399 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 406 def drop_language(name, opts=OPTS) 407 self << drop_language_sql(name, opts) 408 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 415 def drop_schema(name, opts=OPTS) 416 self << drop_schema_sql(name, opts) 417 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 425 def drop_trigger(table, name, opts=OPTS) 426 self << drop_trigger_sql(table, name, opts) 427 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 439 def foreign_key_list(table, opts=OPTS) 440 m = output_identifier_meth 441 schema, _ = opts.fetch(:schema, schema_and_table(table)) 442 oid = regclass_oid(table) 443 reverse = opts[:reverse] 444 445 if reverse 446 ctable = Sequel[:att2] 447 cclass = Sequel[:cl2] 448 rtable = Sequel[:att] 449 rclass = Sequel[:cl] 450 else 451 ctable = Sequel[:att] 452 cclass = Sequel[:cl] 453 rtable = Sequel[:att2] 454 rclass = Sequel[:cl2] 455 end 456 457 if server_version >= 90500 458 cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])} 459 rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])} 460 else 461 range = 0...32 462 cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])} 463 rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])} 464 end 465 466 ds = metadata_dataset. 467 from{pg_constraint.as(:co)}. 468 join(Sequel[:pg_class].as(cclass), :oid=>:conrelid). 469 join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 470 join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]). 471 join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])). 472 join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]). 473 order{[co[:conname], cpos]}. 474 where{{ 475 cl[:relkind]=>'r', 476 co[:contype]=>'f', 477 cl[:oid]=>oid, 478 cpos=>rpos 479 }}. 480 select{[ 481 co[:conname].as(:name), 482 ctable[:attname].as(:column), 483 co[:confupdtype].as(:on_update), 484 co[:confdeltype].as(:on_delete), 485 cl2[:relname].as(:table), 486 rtable[:attname].as(:refcolumn), 487 SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable), 488 nsp[:nspname].as(:schema) 489 ]} 490 491 if reverse 492 ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname]) 493 end 494 495 h = {} 496 fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 497 498 ds.each do |row| 499 if reverse 500 key = [row[:schema], row[:table], row[:name]] 501 else 502 key = row[:name] 503 end 504 505 if r = h[key] 506 r[:columns] << m.call(row[:column]) 507 r[:key] << m.call(row[:refcolumn]) 508 else 509 entry = h[key] = { 510 :name=>m.call(row[:name]), 511 :columns=>[m.call(row[:column])], 512 :key=>[m.call(row[:refcolumn])], 513 :on_update=>fklod_map[row[:on_update]], 514 :on_delete=>fklod_map[row[:on_delete]], 515 :deferrable=>row[:deferrable], 516 :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]), 517 } 518 519 unless schema 520 # If not combining schema information into the :table entry 521 # include it as a separate entry. 522 entry[:schema] = m.call(row[:schema]) 523 end 524 end 525 end 526 527 h.values 528 end
# File lib/sequel/adapters/shared/postgres.rb 530 def freeze 531 server_version 532 supports_prepared_transactions? 533 @conversion_procs.freeze 534 super 535 end
Use the pg_* system tables to determine indexes on a table
# File lib/sequel/adapters/shared/postgres.rb 538 def indexes(table, opts=OPTS) 539 m = output_identifier_meth 540 oid = regclass_oid(table, opts) 541 542 if server_version >= 90500 543 order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])] 544 else 545 range = 0...32 546 order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])] 547 end 548 549 attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey]) 550 551 ds = metadata_dataset. 552 from{pg_class.as(:tab)}. 553 join(Sequel[:pg_index].as(:ind), :indrelid=>:oid). 554 join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid). 555 join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums). 556 left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]). 557 where{{ 558 indc[:relkind]=>'i', 559 ind[:indisprimary]=>false, 560 :indexprs=>nil, 561 :indisvalid=>true, 562 tab[:oid]=>oid}}. 563 order(*order). 564 select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]} 565 566 ds = ds.where(:indpred=>nil) unless opts[:include_partial] 567 ds = ds.where(:indisready=>true) if server_version >= 80300 568 ds = ds.where(:indislive=>true) if server_version >= 90300 569 570 indexes = {} 571 ds.each do |r| 572 i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]} 573 i[:columns] << m.call(r[:column]) 574 end 575 indexes 576 end
Dataset
containing all current database locks
# File lib/sequel/adapters/shared/postgres.rb 579 def locks 580 dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]} 581 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 589 def notify(channel, opts=OPTS) 590 sql = String.new 591 sql << "NOTIFY " 592 dataset.send(:identifier_append, sql, channel) 593 if payload = opts[:payload] 594 sql << ", " 595 dataset.literal_append(sql, payload.to_s) 596 end 597 execute_ddl(sql, opts) 598 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 610 def primary_key_sequence(table, opts=OPTS) 611 quoted_table = quote_schema_table(table) 612 Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)} 613 sql = "#{SELECT_SERIAL_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}" 614 if pks = fetch(sql).single_record 615 value = literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence])) 616 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} 617 else 618 sql = "#{SELECT_CUSTOM_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}" 619 if pks = fetch(sql).single_record 620 value = literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence]))) 621 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} 622 end 623 end 624 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 632 def refresh_view(name, opts=OPTS) 633 run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}" 634 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 638 def reset_primary_key_sequence(table) 639 return unless seq = primary_key_sequence(table) 640 pk = SQL::Identifier.new(primary_key(table)) 641 db = self 642 s, t = schema_and_table(table) 643 table = Sequel.qualify(s, t) if s 644 645 if server_version >= 100000 646 seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq))) 647 increment_by = :seqincrement 648 min_value = :seqmin 649 else 650 seq_ds = metadata_dataset.from(LiteralString.new(seq)) 651 increment_by = :increment_by 652 min_value = :min_value 653 end 654 655 get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)} 656 end
# File lib/sequel/adapters/shared/postgres.rb 658 def rollback_prepared_transaction(transaction_id, opts=OPTS) 659 run("ROLLBACK PREPARED #{literal(transaction_id)}", opts) 660 end
PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.
# File lib/sequel/adapters/shared/postgres.rb 664 def serial_primary_key_options 665 auto_increment_key = server_version >= 100002 ? :identity : :serial 666 {:primary_key => true, auto_increment_key => true, :type=>Integer} 667 end
The version of the PostgreSQL server, used for determining capability.
# File lib/sequel/adapters/shared/postgres.rb 670 def server_version(server=nil) 671 return @server_version if @server_version 672 ds = dataset 673 ds = ds.server(server) if server 674 @server_version ||= ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value rescue 0 675 end
PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+
# File lib/sequel/adapters/shared/postgres.rb 678 def supports_create_table_if_not_exists? 679 server_version >= 90100 680 end
PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 683 def supports_deferrable_constraints? 684 server_version >= 90000 685 end
PostgreSQL supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 688 def supports_deferrable_foreign_key_constraints? 689 true 690 end
PostgreSQL supports DROP TABLE IF EXISTS
# File lib/sequel/adapters/shared/postgres.rb 693 def supports_drop_table_if_exists? 694 true 695 end
PostgreSQL supports partial indexes.
# File lib/sequel/adapters/shared/postgres.rb 698 def supports_partial_indexes? 699 true 700 end
PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.
# File lib/sequel/adapters/shared/postgres.rb 709 def supports_prepared_transactions? 710 return @supports_prepared_transactions if defined?(@supports_prepared_transactions) 711 @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0 712 end
PostgreSQL supports savepoints
# File lib/sequel/adapters/shared/postgres.rb 715 def supports_savepoints? 716 true 717 end
PostgreSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/postgres.rb 720 def supports_transaction_isolation_levels? 721 true 722 end
PostgreSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/postgres.rb 725 def supports_transactional_ddl? 726 true 727 end
PostgreSQL 9.0+ supports trigger conditions.
# File lib/sequel/adapters/shared/postgres.rb 703 def supports_trigger_conditions? 704 server_version >= 90000 705 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 738 def tables(opts=OPTS, &block) 739 pg_class_relname('r', opts, &block) 740 end
Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.
# File lib/sequel/adapters/shared/postgres.rb 744 def type_supported?(type) 745 Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)} 746 supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0 747 Sequel.synchronize{return @supported_types[type] = supported} 748 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 757 def values(v) 758 @default_dataset.clone(:values=>v) 759 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 769 def views(opts=OPTS) 770 relkind = opts[:materialized] ? 'm' : 'v' 771 pg_class_relname(relkind, opts) 772 end
Private Instance Methods
# File lib/sequel/adapters/shared/postgres.rb 776 def alter_table_add_column_sql(table, op) 777 "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}" 778 end
# File lib/sequel/adapters/shared/postgres.rb 794 def alter_table_drop_column_sql(table, op) 795 "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}" 796 end
# File lib/sequel/adapters/shared/postgres.rb 780 def alter_table_generator_class 781 Postgres::AlterTableGenerator 782 end
# File lib/sequel/adapters/shared/postgres.rb 784 def alter_table_set_column_type_sql(table, op) 785 s = super 786 if using = op[:using] 787 using = Sequel::LiteralString.new(using) if using.is_a?(String) 788 s += ' USING ' 789 s << literal(using) 790 end 791 s 792 end
# File lib/sequel/adapters/shared/postgres.rb 798 def alter_table_validate_constraint_sql(table, op) 799 "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}" 800 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 805 def begin_new_transaction(conn, opts) 806 super 807 if opts.has_key?(:synchronous) 808 case sync = opts[:synchronous] 809 when true 810 sync = :on 811 when false 812 sync = :off 813 when nil 814 return 815 end 816 817 log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}") 818 end 819 end
Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.
# File lib/sequel/adapters/shared/postgres.rb 822 def begin_savepoint(conn, opts) 823 super 824 825 unless (read_only = opts[:read_only]).nil? 826 log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}") 827 end 828 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 832 def column_definition_collate_sql(sql, column) 833 if collate = column[:collate] 834 collate = literal(collate) unless collate.is_a?(String) 835 sql << " COLLATE #{collate}" 836 end 837 end
Support identity columns, but only use the identity SQL
syntax if no default value is given.
# File lib/sequel/adapters/shared/postgres.rb 841 def column_definition_default_sql(sql, column) 842 super 843 if !column[:serial] && !['serial', 'bigserial'].include?(column[:type].to_s) && !column[:default] 844 if (identity = column[:identity]) 845 sql << " GENERATED " 846 sql << (identity == :always ? "ALWAYS" : "BY DEFAULT") 847 sql << " AS IDENTITY" 848 elsif (generated = column[:generated_always_as]) 849 sql << " GENERATED ALWAYS AS (#{literal(generated)}) STORED" 850 end 851 end 852 end
Handle PostgreSQL specific default format.
# File lib/sequel/adapters/shared/postgres.rb 855 def column_schema_normalize_default(default, type) 856 if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default) 857 default = m[1] || m[2] 858 end 859 super(default, type) 860 end
PostgreSQL can't combine rename_column operations, and it can combine the custom validate_constraint operation.
# File lib/sequel/adapters/shared/postgres.rb 874 def combinable_alter_table_op?(op) 875 (super || op[:op] == :validate_constraint) && op[:op] != :rename_column 876 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 864 def commit_transaction(conn, opts=OPTS) 865 if (s = opts[:prepare]) && savepoint_level(conn) <= 1 866 log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}") 867 else 868 super 869 end 870 end
The SQL
queries to execute when starting a new connection.
# File lib/sequel/adapters/shared/postgres.rb 880 def connection_configuration_sqls(opts=@opts) 881 sqls = [] 882 883 sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true)) 884 885 cmm = opts.fetch(:client_min_messages, :warning) 886 if cmm && !cmm.to_s.empty? 887 cmm = cmm.to_s.upcase.strip 888 unless VALID_CLIENT_MIN_MESSAGES.include?(cmm) 889 raise Error, "Unsupported client_min_messages setting: #{cmm}" 890 end 891 sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'" 892 end 893 894 if search_path = opts[:search_path] 895 case search_path 896 when String 897 search_path = search_path.split(",").map(&:strip) 898 when Array 899 # nil 900 else 901 raise Error, "unrecognized value for :search_path option: #{search_path.inspect}" 902 end 903 sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}" 904 end 905 906 sqls 907 end
Handle exclusion constraints.
# File lib/sequel/adapters/shared/postgres.rb 910 def constraint_definition_sql(constraint) 911 case constraint[:type] 912 when :exclude 913 elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ') 914 sql = String.new 915 sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}" 916 constraint_deferrable_sql_append(sql, constraint[:deferrable]) 917 sql 918 when :foreign_key, :check 919 sql = super 920 if constraint[:not_valid] 921 sql << " NOT VALID" 922 end 923 sql 924 else 925 super 926 end 927 end
SQL
for doing fast table insert from stdin.
# File lib/sequel/adapters/shared/postgres.rb 958 def copy_into_sql(table, opts) 959 sql = String.new 960 sql << "COPY #{literal(table)}" 961 if cols = opts[:columns] 962 sql << literal(Array(cols)) 963 end 964 sql << " FROM STDIN" 965 if opts[:options] || opts[:format] 966 sql << " (" 967 sql << "FORMAT #{opts[:format]}" if opts[:format] 968 sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 969 sql << ')' 970 end 971 sql 972 end
SQL
for doing fast table output to stdout.
# File lib/sequel/adapters/shared/postgres.rb 975 def copy_table_sql(table, opts) 976 if table.is_a?(String) 977 table 978 else 979 if opts[:options] || opts[:format] 980 options = String.new 981 options << " (" 982 options << "FORMAT #{opts[:format]}" if opts[:format] 983 options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 984 options << ')' 985 end 986 table = if table.is_a?(::Sequel::Dataset) 987 "(#{table.sql})" 988 else 989 literal(table) 990 end 991 "COPY #{table} TO STDOUT#{options}" 992 end 993 end
SQL
statement to create database function.
# File lib/sequel/adapters/shared/postgres.rb 996 def create_function_sql(name, definition, opts=OPTS) 997 args = opts[:args] 998 if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)} 999 returns = opts[:returns] || 'void' 1000 end 1001 language = opts[:language] || 'SQL' 1002 <<-END 1003 CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)} 1004 #{"RETURNS #{returns}" if returns} 1005 LANGUAGE #{language} 1006 #{opts[:behavior].to_s.upcase if opts[:behavior]} 1007 #{'STRICT' if opts[:strict]} 1008 #{'SECURITY DEFINER' if opts[:security_definer]} 1009 #{"COST #{opts[:cost]}" if opts[:cost]} 1010 #{"ROWS #{opts[:rows]}" if opts[:rows]} 1011 #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]} 1012 AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]} 1013 END 1014 end
SQL
for creating a procedural language.
# File lib/sequel/adapters/shared/postgres.rb 1017 def create_language_sql(name, opts=OPTS) 1018 "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]}" 1019 end
SQL
for creating a schema.
# File lib/sequel/adapters/shared/postgres.rb 1022 def create_schema_sql(name, opts=OPTS) 1023 "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}" 1024 end
# File lib/sequel/adapters/shared/postgres.rb 1069 def create_table_as_sql(name, sql, options) 1070 result = create_table_prefix_sql name, options 1071 if on_commit = options[:on_commit] 1072 result += " ON COMMIT #{ON_COMMIT[on_commit]}" 1073 end 1074 result += " AS #{sql}" 1075 end
# File lib/sequel/adapters/shared/postgres.rb 1077 def create_table_generator_class 1078 Postgres::CreateTableGenerator 1079 end
DDL statement for creating a table with the given name, columns, and options
# File lib/sequel/adapters/shared/postgres.rb 1027 def create_table_prefix_sql(name, options) 1028 prefix_sql = if options[:temp] 1029 raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged] 1030 raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign] 1031 temporary_table_sql 1032 elsif options[:foreign] 1033 raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged] 1034 'FOREIGN ' 1035 elsif options[:unlogged] 1036 'UNLOGGED ' 1037 end 1038 1039 "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}" 1040 end
# File lib/sequel/adapters/shared/postgres.rb 1042 def create_table_sql(name, generator, options) 1043 sql = super 1044 1045 if inherits = options[:inherits] 1046 sql += " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})" 1047 end 1048 1049 if on_commit = options[:on_commit] 1050 raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp] 1051 raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit) 1052 sql += " ON COMMIT #{ON_COMMIT[on_commit]}" 1053 end 1054 1055 if tablespace = options[:tablespace] 1056 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1057 end 1058 1059 if server = options[:foreign] 1060 sql += " SERVER #{quote_identifier(server)}" 1061 if foreign_opts = options[:options] 1062 sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})" 1063 end 1064 end 1065 1066 sql 1067 end
SQL
for creating a database trigger.
# File lib/sequel/adapters/shared/postgres.rb 1082 def create_trigger_sql(table, name, function, opts=OPTS) 1083 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 1084 whence = opts[:after] ? 'AFTER' : 'BEFORE' 1085 if filter = opts[:when] 1086 raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions? 1087 filter = " WHEN #{filter_expr(filter)}" 1088 end 1089 "CREATE TRIGGER #{name} #{whence} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)}#{' FOR EACH ROW' if opts[:each_row]}#{filter} EXECUTE PROCEDURE #{function}(#{Array(opts[:args]).map{|a| literal(a)}.join(', ')})" 1090 end
DDL fragment for initial part of CREATE VIEW statement
# File lib/sequel/adapters/shared/postgres.rb 1093 def create_view_prefix_sql(name, options) 1094 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]) 1095 1096 if tablespace = options[:tablespace] 1097 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1098 end 1099 1100 sql 1101 end
# File lib/sequel/adapters/shared/postgres.rb 953 def database_error_regexps 954 DATABASE_ERROR_REGEXPS 955 end
# File lib/sequel/adapters/shared/postgres.rb 929 def database_specific_error_class_from_sqlstate(sqlstate) 930 if sqlstate == '23P01' 931 ExclusionConstraintViolation 932 elsif sqlstate == '40P01' 933 SerializationFailure 934 elsif sqlstate == '55P03' 935 DatabaseLockTimeout 936 else 937 super 938 end 939 end
SQL
for dropping a function from the database.
# File lib/sequel/adapters/shared/postgres.rb 1104 def drop_function_sql(name, opts=OPTS) 1105 "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}" 1106 end
Support :if_exists, :cascade, and :concurrently options.
# File lib/sequel/adapters/shared/postgres.rb 1109 def drop_index_sql(table, op) 1110 sch, _ = schema_and_table(table) 1111 "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]}" 1112 end
SQL
for dropping a procedural language from the database.
# File lib/sequel/adapters/shared/postgres.rb 1115 def drop_language_sql(name, opts=OPTS) 1116 "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}" 1117 end
SQL
for dropping a schema from the database.
# File lib/sequel/adapters/shared/postgres.rb 1120 def drop_schema_sql(name, opts=OPTS) 1121 "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}" 1122 end
Support :foreign tables
# File lib/sequel/adapters/shared/postgres.rb 1130 def drop_table_sql(name, options) 1131 "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}" 1132 end
SQL
for dropping a trigger from the database.
# File lib/sequel/adapters/shared/postgres.rb 1125 def drop_trigger_sql(table, name, opts=OPTS) 1126 "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}" 1127 end
SQL
for dropping a view from the database.
# File lib/sequel/adapters/shared/postgres.rb 1135 def drop_view_sql(name, opts=OPTS) 1136 "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}" 1137 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 1141 def filter_schema(ds, opts) 1142 expr = if schema = opts[:schema] 1143 schema.to_s 1144 else 1145 Sequel.function(:any, Sequel.function(:current_schemas, false)) 1146 end 1147 ds.where{{pg_namespace[:nspname]=>expr}} 1148 end
# File lib/sequel/adapters/shared/postgres.rb 1150 def index_definition_sql(table_name, index) 1151 cols = index[:columns] 1152 index_name = index[:name] || default_index_name(table_name, cols) 1153 expr = if o = index[:opclass] 1154 "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})" 1155 else 1156 literal(Array(cols)) 1157 end 1158 if_not_exists = " IF NOT EXISTS" if index[:if_not_exists] 1159 unique = "UNIQUE " if index[:unique] 1160 index_type = index[:type] 1161 filter = index[:where] || index[:filter] 1162 filter = " WHERE #{filter_expr(filter)}" if filter 1163 case index_type 1164 when :full_text 1165 expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))" 1166 index_type = index[:index_type] || :gin 1167 when :spatial 1168 index_type = :gist 1169 end 1170 "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}" 1171 end
Setup datastructures shared by all postgres adapters.
# File lib/sequel/adapters/shared/postgres.rb 1174 def initialize_postgres_adapter 1175 @primary_keys = {} 1176 @primary_key_sequences = {} 1177 @supported_types = {} 1178 procs = @conversion_procs = CONVERSION_PROCS.dup 1179 procs[1184] = procs[1114] = method(:to_application_timestamp) 1180 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/postgres.rb 1183 def pg_class_relname(type, opts) 1184 ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace) 1185 ds = filter_schema(ds, opts) 1186 m = output_identifier_meth 1187 if block_given? 1188 yield(ds) 1189 elsif opts[:qualify] 1190 ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)} 1191 else 1192 ds.map{|r| m.call(r[:relname])} 1193 end 1194 end
Use a dollar sign instead of question mark for the argument placeholder.
# File lib/sequel/adapters/shared/postgres.rb 1197 def prepared_arg_placeholder 1198 PREPARED_ARG_PLACEHOLDER 1199 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 1203 def regclass_oid(expr, opts=OPTS) 1204 if expr.is_a?(String) && !expr.is_a?(LiteralString) 1205 expr = Sequel.identifier(expr) 1206 end 1207 1208 sch, table = schema_and_table(expr) 1209 sch ||= opts[:schema] 1210 if sch 1211 expr = Sequel.qualify(sch, table) 1212 end 1213 1214 expr = if ds = opts[:dataset] 1215 ds.literal(expr) 1216 else 1217 literal(expr) 1218 end 1219 1220 Sequel.cast(expr.to_s,:regclass).cast(:oid) 1221 end
Remove the cached entries for primary keys and sequences when a table is changed.
# File lib/sequel/adapters/shared/postgres.rb 1224 def remove_cached_schema(table) 1225 tab = quote_schema_table(table) 1226 Sequel.synchronize do 1227 @primary_keys.delete(tab) 1228 @primary_key_sequences.delete(tab) 1229 end 1230 super 1231 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 1235 def rename_table_sql(name, new_name) 1236 "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}" 1237 end
# File lib/sequel/adapters/shared/postgres.rb 1239 def schema_column_type(db_type) 1240 case db_type 1241 when /\Ainterval\z/io 1242 :interval 1243 when /\Acitext\z/io 1244 :string 1245 else 1246 super 1247 end 1248 end
The dataset used for parsing table schemas, using the pg_* system catalogs.
# File lib/sequel/adapters/shared/postgres.rb 1251 def schema_parse_table(table_name, opts) 1252 m = output_identifier_meth(opts[:dataset]) 1253 oid = regclass_oid(table_name, opts) 1254 ds = metadata_dataset.select{[ 1255 pg_attribute[:attname].as(:name), 1256 SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid), 1257 SQL::Cast.new(basetype[:oid], :integer).as(:base_oid), 1258 SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type), 1259 SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type), 1260 SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default), 1261 SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null), 1262 SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key)]}. 1263 from(:pg_class). 1264 join(:pg_attribute, :attrelid=>:oid). 1265 join(:pg_type, :oid=>:atttypid). 1266 left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype). 1267 left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]). 1268 left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true). 1269 where{{pg_attribute[:attisdropped]=>false}}. 1270 where{pg_attribute[:attnum] > 0}. 1271 where{{pg_class[:oid]=>oid}}. 1272 order{pg_attribute[:attnum]} 1273 1274 if server_version > 100000 1275 ds = ds.select_append{pg_attribute[:attidentity]} 1276 end 1277 1278 ds.map do |row| 1279 row[:default] = nil if blank_object?(row[:default]) 1280 if row[:base_oid] 1281 row[:domain_oid] = row[:oid] 1282 row[:oid] = row.delete(:base_oid) 1283 row[:db_domain_type] = row[:db_type] 1284 row[:db_type] = row.delete(:db_base_type) 1285 else 1286 row.delete(:base_oid) 1287 row.delete(:db_base_type) 1288 end 1289 row[:type] = schema_column_type(row[:db_type]) 1290 identity = row.delete(:attidentity) 1291 if row[:primary_key] 1292 row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd' 1293 end 1294 [m.call(row.delete(:name)), row] 1295 end 1296 end
Set the transaction isolation level on the given connection
# File lib/sequel/adapters/shared/postgres.rb 1299 def set_transaction_isolation(conn, opts) 1300 level = opts.fetch(:isolation, transaction_isolation_level) 1301 read_only = opts[:read_only] 1302 deferrable = opts[:deferrable] 1303 if level || !read_only.nil? || !deferrable.nil? 1304 sql = String.new 1305 sql << "SET TRANSACTION" 1306 sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level 1307 sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil? 1308 sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil? 1309 log_connection_execute(conn, sql) 1310 end 1311 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 1314 def sql_function_args(args) 1315 "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})" 1316 end
PostgreSQL can combine multiple alter table ops into a single query.
# File lib/sequel/adapters/shared/postgres.rb 1319 def supports_combining_alter_table_ops? 1320 true 1321 end
PostgreSQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/postgres.rb 1324 def supports_create_or_replace_view? 1325 true 1326 end
Handle bigserial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1329 def type_literal_generic_bignum_symbol(column) 1330 column[:serial] ? :bigserial : super 1331 end
PostgreSQL uses the bytea data type for blobs
# File lib/sequel/adapters/shared/postgres.rb 1334 def type_literal_generic_file(column) 1335 :bytea 1336 end
Handle serial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1339 def type_literal_generic_integer(column) 1340 column[:serial] ? :serial : super 1341 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 1347 def type_literal_generic_string(column) 1348 if column[:fixed] 1349 "char(#{column[:size]||255})" 1350 elsif column[:text] == false or column[:size] 1351 "varchar(#{column[:size]||255})" 1352 else 1353 :text 1354 end 1355 end
PostgreSQL 9.4+ supports views with check option.
# File lib/sequel/adapters/shared/postgres.rb 1358 def view_with_check_option_support 1359 :local if server_version >= 90400 1360 end