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