# File lib/sequel/adapters/shared/postgres.rb 597 def primary_key(table, opts=OPTS) 598 quoted_table = quote_schema_table(table) 599 Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)} 600 sql = "#{SELECT_PK_SQL} AND pg_class.oid = #{literal(regclass_oid(table, opts))}" 601 value = fetch(sql).single_value 602 Sequel.synchronize{@primary_keys[quoted_table] = value} 603 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 202 def add_conversion_proc(oid, callable=nil, &block) 203 conversion_procs[oid] = callable || block 204 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 209 def add_named_conversion_proc(name, &block) 210 unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid) 211 raise Error, "No matching type in pg_type for #{name.inspect}" 212 end 213 add_conversion_proc(oid, block) 214 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 225 def check_constraints(table) 226 m = output_identifier_meth 227 228 rows = metadata_dataset. 229 from{pg_constraint.as(:co)}. 230 left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 231 where(:conrelid=>regclass_oid(table), :contype=>'c'). 232 select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]} 233 234 hash = {} 235 rows.each do |row| 236 constraint = m.call(row[:constraint]) 237 entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[]} 238 entry[:columns] << m.call(row[:column]) if row[:column] 239 end 240 241 hash 242 end
# File lib/sequel/adapters/shared/postgres.rb 216 def commit_prepared_transaction(transaction_id, opts=OPTS) 217 run("COMMIT PREPARED #{literal(transaction_id)}", opts) 218 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 262 def convert_serial_to_identity(table, opts=OPTS) 263 raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002 264 265 server = opts[:server] 266 server_hash = server ? {:server=>server} : OPTS 267 ds = dataset 268 ds = ds.server(server) if server 269 270 raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on' 271 272 table_oid = regclass_oid(table) 273 im = input_identifier_meth 274 unless column = im.call(opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0])) 275 raise Error, "could not determine column to convert from serial to identity automatically" 276 end 277 278 column_num = ds.from(:pg_attribute). 279 where(:attrelid=>table_oid, :attname=>column). 280 get(:attnum) 281 282 pg_class = Sequel.cast('pg_class', :regclass) 283 res = ds.from(:pg_depend). 284 where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i'). 285 select_map([:objid, Sequel.as({:deptype=>'i'}, :v)]) 286 287 case res.length 288 when 0 289 raise Error, "unable to find related sequence when converting serial to identity" 290 when 1 291 seq_oid, already_identity = res.first 292 else 293 raise Error, "more than one linked sequence found when converting serial to identity" 294 end 295 296 return if already_identity 297 298 transaction(server_hash) do 299 run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT", server_hash) 300 301 ds.from(:pg_depend). 302 where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a'). 303 update(:deptype=>'i') 304 305 ds.from(:pg_attribute). 306 where(:attrelid=>table_oid, :attname=>column). 307 update(:attidentity=>'d') 308 end 309 310 remove_cached_schema(table) 311 nil 312 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 334 def create_function(name, definition, opts=OPTS) 335 self << create_function_sql(name, definition, opts) 336 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 345 def create_language(name, opts=OPTS) 346 self << create_language_sql(name, opts) 347 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 354 def create_schema(name, opts=OPTS) 355 self << create_schema_sql(name, opts) 356 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 369 def create_trigger(table, name, function, opts=OPTS) 370 self << create_trigger_sql(table, name, function, opts) 371 end
# File lib/sequel/adapters/shared/postgres.rb 373 def database_type 374 :postgres 375 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 382 def do(code, opts=OPTS) 383 language = opts[:language] 384 run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}" 385 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 393 def drop_function(name, opts=OPTS) 394 self << drop_function_sql(name, opts) 395 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 402 def drop_language(name, opts=OPTS) 403 self << drop_language_sql(name, opts) 404 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 411 def drop_schema(name, opts=OPTS) 412 self << drop_schema_sql(name, opts) 413 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 421 def drop_trigger(table, name, opts=OPTS) 422 self << drop_trigger_sql(table, name, opts) 423 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 435 def foreign_key_list(table, opts=OPTS) 436 m = output_identifier_meth 437 schema, _ = opts.fetch(:schema, schema_and_table(table)) 438 oid = regclass_oid(table) 439 reverse = opts[:reverse] 440 441 if reverse 442 ctable = Sequel[:att2] 443 cclass = Sequel[:cl2] 444 rtable = Sequel[:att] 445 rclass = Sequel[:cl] 446 else 447 ctable = Sequel[:att] 448 cclass = Sequel[:cl] 449 rtable = Sequel[:att2] 450 rclass = Sequel[:cl2] 451 end 452 453 if server_version >= 90500 454 cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])} 455 rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])} 456 else 457 range = 0...32 458 cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])} 459 rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])} 460 end 461 462 ds = metadata_dataset. 463 from{pg_constraint.as(:co)}. 464 join(Sequel[:pg_class].as(cclass), :oid=>:conrelid). 465 join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])). 466 join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]). 467 join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])). 468 join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]). 469 order{[co[:conname], cpos]}. 470 where{{ 471 cl[:relkind]=>'r', 472 co[:contype]=>'f', 473 cl[:oid]=>oid, 474 cpos=>rpos 475 }}. 476 select{[ 477 co[:conname].as(:name), 478 ctable[:attname].as(:column), 479 co[:confupdtype].as(:on_update), 480 co[:confdeltype].as(:on_delete), 481 cl2[:relname].as(:table), 482 rtable[:attname].as(:refcolumn), 483 SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable), 484 nsp[:nspname].as(:schema) 485 ]} 486 487 if reverse 488 ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname]) 489 end 490 491 h = {} 492 fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 493 494 ds.each do |row| 495 if reverse 496 key = [row[:schema], row[:table], row[:name]] 497 else 498 key = row[:name] 499 end 500 501 if r = h[key] 502 r[:columns] << m.call(row[:column]) 503 r[:key] << m.call(row[:refcolumn]) 504 else 505 entry = h[key] = { 506 :name=>m.call(row[:name]), 507 :columns=>[m.call(row[:column])], 508 :key=>[m.call(row[:refcolumn])], 509 :on_update=>fklod_map[row[:on_update]], 510 :on_delete=>fklod_map[row[:on_delete]], 511 :deferrable=>row[:deferrable], 512 :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]), 513 } 514 515 unless schema 516 # If not combining schema information into the :table entry 517 # include it as a separate entry. 518 entry[:schema] = m.call(row[:schema]) 519 end 520 end 521 end 522 523 h.values 524 end
# File lib/sequel/adapters/shared/postgres.rb 526 def freeze 527 server_version 528 supports_prepared_transactions? 529 @conversion_procs.freeze 530 super 531 end
Use the pg_* system tables to determine indexes on a table
# File lib/sequel/adapters/shared/postgres.rb 534 def indexes(table, opts=OPTS) 535 m = output_identifier_meth 536 oid = regclass_oid(table, opts) 537 538 if server_version >= 90500 539 order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])] 540 else 541 range = 0...32 542 order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])] 543 end 544 545 attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey]) 546 547 ds = metadata_dataset. 548 from{pg_class.as(:tab)}. 549 join(Sequel[:pg_index].as(:ind), :indrelid=>:oid). 550 join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid). 551 join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums). 552 left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]). 553 where{{ 554 indc[:relkind]=>'i', 555 ind[:indisprimary]=>false, 556 :indexprs=>nil, 557 :indisvalid=>true, 558 tab[:oid]=>oid}}. 559 order(*order). 560 select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]} 561 562 ds = ds.where(:indpred=>nil) unless opts[:include_partial] 563 ds = ds.where(:indisready=>true) if server_version >= 80300 564 ds = ds.where(:indislive=>true) if server_version >= 90300 565 566 indexes = {} 567 ds.each do |r| 568 i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]} 569 i[:columns] << m.call(r[:column]) 570 end 571 indexes 572 end
Dataset
containing all current database locks
# File lib/sequel/adapters/shared/postgres.rb 575 def locks 576 dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]} 577 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 585 def notify(channel, opts=OPTS) 586 sql = String.new 587 sql << "NOTIFY " 588 dataset.send(:identifier_append, sql, channel) 589 if payload = opts[:payload] 590 sql << ", " 591 dataset.literal_append(sql, payload.to_s) 592 end 593 execute_ddl(sql, opts) 594 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 606 def primary_key_sequence(table, opts=OPTS) 607 quoted_table = quote_schema_table(table) 608 Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)} 609 sql = "#{SELECT_SERIAL_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}" 610 if pks = fetch(sql).single_record 611 value = literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence])) 612 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} 613 else 614 sql = "#{SELECT_CUSTOM_SEQUENCE_SQL} AND t.oid = #{literal(regclass_oid(table, opts))}" 615 if pks = fetch(sql).single_record 616 value = literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence]))) 617 Sequel.synchronize{@primary_key_sequences[quoted_table] = value} 618 end 619 end 620 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 628 def refresh_view(name, opts=OPTS) 629 run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}" 630 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 634 def reset_primary_key_sequence(table) 635 return unless seq = primary_key_sequence(table) 636 pk = SQL::Identifier.new(primary_key(table)) 637 db = self 638 s, t = schema_and_table(table) 639 table = Sequel.qualify(s, t) if s 640 641 if server_version >= 100000 642 seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq))) 643 increment_by = :seqincrement 644 min_value = :seqmin 645 else 646 seq_ds = metadata_dataset.from(LiteralString.new(seq)) 647 increment_by = :increment_by 648 min_value = :min_value 649 end 650 651 get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)} 652 end
# File lib/sequel/adapters/shared/postgres.rb 654 def rollback_prepared_transaction(transaction_id, opts=OPTS) 655 run("ROLLBACK PREPARED #{literal(transaction_id)}", opts) 656 end
PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.
# File lib/sequel/adapters/shared/postgres.rb 660 def serial_primary_key_options 661 auto_increment_key = server_version >= 100002 ? :identity : :serial 662 {:primary_key => true, auto_increment_key => true, :type=>Integer} 663 end
The version of the PostgreSQL server, used for determining capability.
# File lib/sequel/adapters/shared/postgres.rb 666 def server_version(server=nil) 667 return @server_version if @server_version 668 ds = dataset 669 ds = ds.server(server) if server 670 @server_version ||= ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value rescue 0 671 end
PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+
# File lib/sequel/adapters/shared/postgres.rb 674 def supports_create_table_if_not_exists? 675 server_version >= 90100 676 end
PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 679 def supports_deferrable_constraints? 680 server_version >= 90000 681 end
PostgreSQL supports deferrable foreign key constraints.
# File lib/sequel/adapters/shared/postgres.rb 684 def supports_deferrable_foreign_key_constraints? 685 true 686 end
PostgreSQL supports DROP TABLE IF EXISTS
# File lib/sequel/adapters/shared/postgres.rb 689 def supports_drop_table_if_exists? 690 true 691 end
PostgreSQL supports partial indexes.
# File lib/sequel/adapters/shared/postgres.rb 694 def supports_partial_indexes? 695 true 696 end
PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.
# File lib/sequel/adapters/shared/postgres.rb 705 def supports_prepared_transactions? 706 return @supports_prepared_transactions if defined?(@supports_prepared_transactions) 707 @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0 708 end
PostgreSQL supports savepoints
# File lib/sequel/adapters/shared/postgres.rb 711 def supports_savepoints? 712 true 713 end
PostgreSQL supports transaction isolation levels
# File lib/sequel/adapters/shared/postgres.rb 716 def supports_transaction_isolation_levels? 717 true 718 end
PostgreSQL supports transaction DDL statements.
# File lib/sequel/adapters/shared/postgres.rb 721 def supports_transactional_ddl? 722 true 723 end
PostgreSQL 9.0+ supports trigger conditions.
# File lib/sequel/adapters/shared/postgres.rb 699 def supports_trigger_conditions? 700 server_version >= 90000 701 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 734 def tables(opts=OPTS, &block) 735 pg_class_relname('r', opts, &block) 736 end
Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.
# File lib/sequel/adapters/shared/postgres.rb 740 def type_supported?(type) 741 Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)} 742 supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0 743 Sequel.synchronize{return @supported_types[type] = supported} 744 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 753 def values(v) 754 @default_dataset.clone(:values=>v) 755 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 765 def views(opts=OPTS) 766 relkind = opts[:materialized] ? 'm' : 'v' 767 pg_class_relname(relkind, opts) 768 end
Private Instance Methods
# File lib/sequel/adapters/shared/postgres.rb 772 def alter_table_add_column_sql(table, op) 773 "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}" 774 end
# File lib/sequel/adapters/shared/postgres.rb 790 def alter_table_drop_column_sql(table, op) 791 "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}" 792 end
# File lib/sequel/adapters/shared/postgres.rb 776 def alter_table_generator_class 777 Postgres::AlterTableGenerator 778 end
# File lib/sequel/adapters/shared/postgres.rb 780 def alter_table_set_column_type_sql(table, op) 781 s = super 782 if using = op[:using] 783 using = Sequel::LiteralString.new(using) if using.is_a?(String) 784 s += ' USING ' 785 s << literal(using) 786 end 787 s 788 end
# File lib/sequel/adapters/shared/postgres.rb 794 def alter_table_validate_constraint_sql(table, op) 795 "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}" 796 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 801 def begin_new_transaction(conn, opts) 802 super 803 if opts.has_key?(:synchronous) 804 case sync = opts[:synchronous] 805 when true 806 sync = :on 807 when false 808 sync = :off 809 when nil 810 return 811 end 812 813 log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}") 814 end 815 end
Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.
# File lib/sequel/adapters/shared/postgres.rb 818 def begin_savepoint(conn, opts) 819 super 820 821 unless (read_only = opts[:read_only]).nil? 822 log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}") 823 end 824 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 828 def column_definition_collate_sql(sql, column) 829 if collate = column[:collate] 830 collate = literal(collate) unless collate.is_a?(String) 831 sql << " COLLATE #{collate}" 832 end 833 end
Support identity columns, but only use the identity SQL
syntax if no default value is given.
# File lib/sequel/adapters/shared/postgres.rb 837 def column_definition_default_sql(sql, column) 838 super 839 if !column[:serial] && !['serial', 'bigserial'].include?(column[:type].to_s) && !column[:default] && (identity = column[:identity]) 840 sql << " GENERATED " 841 sql << (identity == :always ? "ALWAYS" : "BY DEFAULT") 842 sql << " AS IDENTITY" 843 end 844 end
Handle PostgreSQL specific default format.
# File lib/sequel/adapters/shared/postgres.rb 847 def column_schema_normalize_default(default, type) 848 if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default) 849 default = m[1] || m[2] 850 end 851 super(default, type) 852 end
PostgreSQL can't combine rename_column operations, and it can combine the custom validate_constraint operation.
# File lib/sequel/adapters/shared/postgres.rb 866 def combinable_alter_table_op?(op) 867 (super || op[:op] == :validate_constraint) && op[:op] != :rename_column 868 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 856 def commit_transaction(conn, opts=OPTS) 857 if (s = opts[:prepare]) && savepoint_level(conn) <= 1 858 log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}") 859 else 860 super 861 end 862 end
The SQL
queries to execute when starting a new connection.
# File lib/sequel/adapters/shared/postgres.rb 872 def connection_configuration_sqls(opts=@opts) 873 sqls = [] 874 875 sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true)) 876 877 cmm = opts.fetch(:client_min_messages, :warning) 878 if cmm && !cmm.to_s.empty? 879 cmm = cmm.to_s.upcase.strip 880 unless VALID_CLIENT_MIN_MESSAGES.include?(cmm) 881 raise Error, "Unsupported client_min_messages setting: #{cmm}" 882 end 883 sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'" 884 end 885 886 if search_path = opts[:search_path] 887 case search_path 888 when String 889 search_path = search_path.split(",").map(&:strip) 890 when Array 891 # nil 892 else 893 raise Error, "unrecognized value for :search_path option: #{search_path.inspect}" 894 end 895 sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}" 896 end 897 898 sqls 899 end
Handle exclusion constraints.
# File lib/sequel/adapters/shared/postgres.rb 902 def constraint_definition_sql(constraint) 903 case constraint[:type] 904 when :exclude 905 elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ') 906 sql = String.new 907 sql << "#{"CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]}EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})#{" WHERE #{filter_expr(constraint[:where])}" if constraint[:where]}" 908 constraint_deferrable_sql_append(sql, constraint[:deferrable]) 909 sql 910 when :foreign_key, :check 911 sql = super 912 if constraint[:not_valid] 913 sql << " NOT VALID" 914 end 915 sql 916 else 917 super 918 end 919 end
SQL
for doing fast table insert from stdin.
# File lib/sequel/adapters/shared/postgres.rb 950 def copy_into_sql(table, opts) 951 sql = String.new 952 sql << "COPY #{literal(table)}" 953 if cols = opts[:columns] 954 sql << literal(Array(cols)) 955 end 956 sql << " FROM STDIN" 957 if opts[:options] || opts[:format] 958 sql << " (" 959 sql << "FORMAT #{opts[:format]}" if opts[:format] 960 sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 961 sql << ')' 962 end 963 sql 964 end
SQL
for doing fast table output to stdout.
# File lib/sequel/adapters/shared/postgres.rb 967 def copy_table_sql(table, opts) 968 if table.is_a?(String) 969 table 970 else 971 if opts[:options] || opts[:format] 972 options = String.new 973 options << " (" 974 options << "FORMAT #{opts[:format]}" if opts[:format] 975 options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options] 976 options << ')' 977 end 978 table = if table.is_a?(::Sequel::Dataset) 979 "(#{table.sql})" 980 else 981 literal(table) 982 end 983 "COPY #{table} TO STDOUT#{options}" 984 end 985 end
SQL
statement to create database function.
# File lib/sequel/adapters/shared/postgres.rb 988 def create_function_sql(name, definition, opts=OPTS) 989 args = opts[:args] 990 if !opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 and %w'OUT INOUT'.include?(a[2].to_s)} 991 returns = opts[:returns] || 'void' 992 end 993 language = opts[:language] || 'SQL' 994 <<-END 995 CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)} 996 #{"RETURNS #{returns}" if returns} 997 LANGUAGE #{language} 998 #{opts[:behavior].to_s.upcase if opts[:behavior]} 999 #{'STRICT' if opts[:strict]} 1000 #{'SECURITY DEFINER' if opts[:security_definer]} 1001 #{"COST #{opts[:cost]}" if opts[:cost]} 1002 #{"ROWS #{opts[:rows]}" if opts[:rows]} 1003 #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]} 1004 AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]} 1005 END 1006 end
SQL
for creating a procedural language.
# File lib/sequel/adapters/shared/postgres.rb 1009 def create_language_sql(name, opts=OPTS) 1010 "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]}" 1011 end
SQL
for creating a schema.
# File lib/sequel/adapters/shared/postgres.rb 1014 def create_schema_sql(name, opts=OPTS) 1015 "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}" 1016 end
# File lib/sequel/adapters/shared/postgres.rb 1061 def create_table_as_sql(name, sql, options) 1062 result = create_table_prefix_sql name, options 1063 if on_commit = options[:on_commit] 1064 result += " ON COMMIT #{ON_COMMIT[on_commit]}" 1065 end 1066 result += " AS #{sql}" 1067 end
# File lib/sequel/adapters/shared/postgres.rb 1069 def create_table_generator_class 1070 Postgres::CreateTableGenerator 1071 end
DDL statement for creating a table with the given name, columns, and options
# File lib/sequel/adapters/shared/postgres.rb 1019 def create_table_prefix_sql(name, options) 1020 prefix_sql = if options[:temp] 1021 raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged] 1022 raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign] 1023 temporary_table_sql 1024 elsif options[:foreign] 1025 raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged] 1026 'FOREIGN ' 1027 elsif options[:unlogged] 1028 'UNLOGGED ' 1029 end 1030 1031 "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{options[:temp] ? quote_identifier(name) : quote_schema_table(name)}" 1032 end
# File lib/sequel/adapters/shared/postgres.rb 1034 def create_table_sql(name, generator, options) 1035 sql = super 1036 1037 if inherits = options[:inherits] 1038 sql += " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})" 1039 end 1040 1041 if on_commit = options[:on_commit] 1042 raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp] 1043 raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit) 1044 sql += " ON COMMIT #{ON_COMMIT[on_commit]}" 1045 end 1046 1047 if tablespace = options[:tablespace] 1048 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1049 end 1050 1051 if server = options[:foreign] 1052 sql += " SERVER #{quote_identifier(server)}" 1053 if foreign_opts = options[:options] 1054 sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})" 1055 end 1056 end 1057 1058 sql 1059 end
SQL
for creating a database trigger.
# File lib/sequel/adapters/shared/postgres.rb 1074 def create_trigger_sql(table, name, function, opts=OPTS) 1075 events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] 1076 whence = opts[:after] ? 'AFTER' : 'BEFORE' 1077 if filter = opts[:when] 1078 raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions? 1079 filter = " WHEN #{filter_expr(filter)}" 1080 end 1081 "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(', ')})" 1082 end
DDL fragment for initial part of CREATE VIEW statement
# File lib/sequel/adapters/shared/postgres.rb 1085 def create_view_prefix_sql(name, options) 1086 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]) 1087 1088 if tablespace = options[:tablespace] 1089 sql += " TABLESPACE #{quote_identifier(tablespace)}" 1090 end 1091 1092 sql 1093 end
# File lib/sequel/adapters/shared/postgres.rb 945 def database_error_regexps 946 DATABASE_ERROR_REGEXPS 947 end
# File lib/sequel/adapters/shared/postgres.rb 921 def database_specific_error_class_from_sqlstate(sqlstate) 922 if sqlstate == '23P01' 923 ExclusionConstraintViolation 924 elsif sqlstate == '40P01' 925 SerializationFailure 926 elsif sqlstate == '55P03' 927 DatabaseLockTimeout 928 else 929 super 930 end 931 end
SQL
for dropping a function from the database.
# File lib/sequel/adapters/shared/postgres.rb 1096 def drop_function_sql(name, opts=OPTS) 1097 "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}" 1098 end
Support :if_exists, :cascade, and :concurrently options.
# File lib/sequel/adapters/shared/postgres.rb 1101 def drop_index_sql(table, op) 1102 sch, _ = schema_and_table(table) 1103 "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]}" 1104 end
SQL
for dropping a procedural language from the database.
# File lib/sequel/adapters/shared/postgres.rb 1107 def drop_language_sql(name, opts=OPTS) 1108 "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}" 1109 end
SQL
for dropping a schema from the database.
# File lib/sequel/adapters/shared/postgres.rb 1112 def drop_schema_sql(name, opts=OPTS) 1113 "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}" 1114 end
Support :foreign tables
# File lib/sequel/adapters/shared/postgres.rb 1122 def drop_table_sql(name, options) 1123 "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}" 1124 end
SQL
for dropping a trigger from the database.
# File lib/sequel/adapters/shared/postgres.rb 1117 def drop_trigger_sql(table, name, opts=OPTS) 1118 "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}" 1119 end
SQL
for dropping a view from the database.
# File lib/sequel/adapters/shared/postgres.rb 1127 def drop_view_sql(name, opts=OPTS) 1128 "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}" 1129 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 1133 def filter_schema(ds, opts) 1134 expr = if schema = opts[:schema] 1135 schema.to_s 1136 else 1137 Sequel.function(:any, Sequel.function(:current_schemas, false)) 1138 end 1139 ds.where{{pg_namespace[:nspname]=>expr}} 1140 end
# File lib/sequel/adapters/shared/postgres.rb 1142 def index_definition_sql(table_name, index) 1143 cols = index[:columns] 1144 index_name = index[:name] || default_index_name(table_name, cols) 1145 expr = if o = index[:opclass] 1146 "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})" 1147 else 1148 literal(Array(cols)) 1149 end 1150 if_not_exists = " IF NOT EXISTS" if index[:if_not_exists] 1151 unique = "UNIQUE " if index[:unique] 1152 index_type = index[:type] 1153 filter = index[:where] || index[:filter] 1154 filter = " WHERE #{filter_expr(filter)}" if filter 1155 case index_type 1156 when :full_text 1157 expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))" 1158 index_type = index[:index_type] || :gin 1159 when :spatial 1160 index_type = :gist 1161 end 1162 "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}" 1163 end
Setup datastructures shared by all postgres adapters.
# File lib/sequel/adapters/shared/postgres.rb 1166 def initialize_postgres_adapter 1167 @primary_keys = {} 1168 @primary_key_sequences = {} 1169 @supported_types = {} 1170 procs = @conversion_procs = CONVERSION_PROCS.dup 1171 procs[1184] = procs[1114] = method(:to_application_timestamp) 1172 end
Backbone of the tables and views support.
# File lib/sequel/adapters/shared/postgres.rb 1175 def pg_class_relname(type, opts) 1176 ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace) 1177 ds = filter_schema(ds, opts) 1178 m = output_identifier_meth 1179 if block_given? 1180 yield(ds) 1181 elsif opts[:qualify] 1182 ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)} 1183 else 1184 ds.map{|r| m.call(r[:relname])} 1185 end 1186 end
Use a dollar sign instead of question mark for the argument placeholder.
# File lib/sequel/adapters/shared/postgres.rb 1189 def prepared_arg_placeholder 1190 PREPARED_ARG_PLACEHOLDER 1191 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 1195 def regclass_oid(expr, opts=OPTS) 1196 if expr.is_a?(String) && !expr.is_a?(LiteralString) 1197 expr = Sequel.identifier(expr) 1198 end 1199 1200 sch, table = schema_and_table(expr) 1201 sch ||= opts[:schema] 1202 if sch 1203 expr = Sequel.qualify(sch, table) 1204 end 1205 1206 expr = if ds = opts[:dataset] 1207 ds.literal(expr) 1208 else 1209 literal(expr) 1210 end 1211 1212 Sequel.cast(expr.to_s,:regclass).cast(:oid) 1213 end
Remove the cached entries for primary keys and sequences when a table is changed.
# File lib/sequel/adapters/shared/postgres.rb 1216 def remove_cached_schema(table) 1217 tab = quote_schema_table(table) 1218 Sequel.synchronize do 1219 @primary_keys.delete(tab) 1220 @primary_key_sequences.delete(tab) 1221 end 1222 super 1223 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 1227 def rename_table_sql(name, new_name) 1228 "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}" 1229 end
# File lib/sequel/adapters/shared/postgres.rb 1231 def schema_column_type(db_type) 1232 case db_type 1233 when /\Ainterval\z/io 1234 :interval 1235 when /\Acitext\z/io 1236 :string 1237 else 1238 super 1239 end 1240 end
The dataset used for parsing table schemas, using the pg_* system catalogs.
# File lib/sequel/adapters/shared/postgres.rb 1243 def schema_parse_table(table_name, opts) 1244 m = output_identifier_meth(opts[:dataset]) 1245 oid = regclass_oid(table_name, opts) 1246 ds = metadata_dataset.select{[ 1247 pg_attribute[:attname].as(:name), 1248 SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid), 1249 SQL::Cast.new(basetype[:oid], :integer).as(:base_oid), 1250 SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type), 1251 SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type), 1252 SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default), 1253 SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null), 1254 SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key)]}. 1255 from(:pg_class). 1256 join(:pg_attribute, :attrelid=>:oid). 1257 join(:pg_type, :oid=>:atttypid). 1258 left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype). 1259 left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]). 1260 left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true). 1261 where{{pg_attribute[:attisdropped]=>false}}. 1262 where{pg_attribute[:attnum] > 0}. 1263 where{{pg_class[:oid]=>oid}}. 1264 order{pg_attribute[:attnum]} 1265 1266 if server_version > 100000 1267 ds = ds.select_append{pg_attribute[:attidentity]} 1268 end 1269 1270 ds.map do |row| 1271 row[:default] = nil if blank_object?(row[:default]) 1272 if row[:base_oid] 1273 row[:domain_oid] = row[:oid] 1274 row[:oid] = row.delete(:base_oid) 1275 row[:db_domain_type] = row[:db_type] 1276 row[:db_type] = row.delete(:db_base_type) 1277 else 1278 row.delete(:base_oid) 1279 row.delete(:db_base_type) 1280 end 1281 row[:type] = schema_column_type(row[:db_type]) 1282 identity = row.delete(:attidentity) 1283 if row[:primary_key] 1284 row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd' 1285 end 1286 [m.call(row.delete(:name)), row] 1287 end 1288 end
Set the transaction isolation level on the given connection
# File lib/sequel/adapters/shared/postgres.rb 1291 def set_transaction_isolation(conn, opts) 1292 level = opts.fetch(:isolation, transaction_isolation_level) 1293 read_only = opts[:read_only] 1294 deferrable = opts[:deferrable] 1295 if level || !read_only.nil? || !deferrable.nil? 1296 sql = String.new 1297 sql << "SET TRANSACTION" 1298 sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level 1299 sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil? 1300 sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil? 1301 log_connection_execute(conn, sql) 1302 end 1303 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 1306 def sql_function_args(args) 1307 "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})" 1308 end
PostgreSQL can combine multiple alter table ops into a single query.
# File lib/sequel/adapters/shared/postgres.rb 1311 def supports_combining_alter_table_ops? 1312 true 1313 end
PostgreSQL supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/postgres.rb 1316 def supports_create_or_replace_view? 1317 true 1318 end
Handle bigserial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1321 def type_literal_generic_bignum_symbol(column) 1322 column[:serial] ? :bigserial : super 1323 end
PostgreSQL uses the bytea data type for blobs
# File lib/sequel/adapters/shared/postgres.rb 1326 def type_literal_generic_file(column) 1327 :bytea 1328 end
Handle serial type if :serial option is present
# File lib/sequel/adapters/shared/postgres.rb 1331 def type_literal_generic_integer(column) 1332 column[:serial] ? :serial : super 1333 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 1339 def type_literal_generic_string(column) 1340 if column[:fixed] 1341 "char(#{column[:size]||255})" 1342 elsif column[:text] == false or column[:size] 1343 "varchar(#{column[:size]||255})" 1344 else 1345 :text 1346 end 1347 end
PostgreSQL 9.4+ supports views with check option.
# File lib/sequel/adapters/shared/postgres.rb 1350 def view_with_check_option_support 1351 :local if server_version >= 90400 1352 end