The PostgreSQL adapter works both with the native C (ruby.scripting.ca/postgres/) and the pure Ruby (available both as gem and from rubyforge.org/frs/?group_id=234&release_id=1944) drivers.
Options:
-
:host
- Defaults to “localhost”. -
:port
- Defaults to 5432. -
:username
- Defaults to nothing. -
:password
- Defaults to nothing. -
:database
- The name of the database. No default, must be provided. -
:schema_search_path
- An optional schema search path for the connection given as a string of comma-separated schema names. This is backward-compatible with the:schema_order
option. -
:encoding
- An optional client encoding that is used in aSET client_encoding TO <encoding>
call on the connection. -
:min_messages
- An optional client min messages that is used in aSET client_min_messages TO <min_messages>
call on the connection.
- CLASS ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::StatementPool
- CLASS ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::TableDefinition
- A
- B
- C
- D
- E
- I
- N
- O
- P
- Q
- R
- S
- T
- U
ADAPTER_NAME | = | 'PostgreSQL' |
NATIVE_DATABASE_TYPES | = | { :primary_key => "serial primary key", :string => { :name => "character varying", :limit => 255 }, :text => { :name => "text" }, :integer => { :name => "integer" }, :float => { :name => "float" }, :decimal => { :name => "decimal" }, :datetime => { :name => "timestamp" }, :timestamp => { :name => "timestamp" }, :time => { :name => "time" }, :date => { :name => "date" }, :binary => { :name => "bytea" }, :boolean => { :name => "boolean" }, :xml => { :name => "xml" }, :tsvector => { :name => "tsvector" } } |
FEATURE_NOT_SUPPORTED | = | "0A000" |
MONEY_COLUMN_TYPE_OID | = | 790 |
The internal PostgreSQL identifier of the money data type. |
||
BYTEA_COLUMN_TYPE_OID | = | 17 |
The internal PostgreSQL identifier of the BYTEA data type. |
Initializes and connects a PostgreSQL adapter.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 304 def initialize(connection, logger, connection_parameters, config) super(connection, logger) @connection_parameters, @config = connection_parameters, config # @local_tz is initialized as nil to avoid warnings when connect tries to use it @local_tz = nil @table_alias_length = nil connect @statements = StatementPool.new @connection, config.fetch(:statement_limit) { 1000 } if postgresql_version < 80200 raise "Your version of PostgreSQL (#{postgresql_version}) is too old, please upgrade!" end @local_tz = execute('SHOW TIME ZONE', 'SCHEMA').first["TimeZone"] end
Is this connection alive and ready for queries?
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 333 def active? @connection.status == PGconn::CONNECTION_OK rescue PGError false end
Returns ‘PostgreSQL’ as adapter name for identification purposes.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 241 def adapter_name ADAPTER_NAME end
Adds a new column to the named table. See ActiveRecord::ConnectionAdapters::TableDefinition#column for details of the options you can use.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 918 def add_column(table_name, column_name, type, options = {}) clear_cache! add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" add_column_options!(add_column_sql, options) execute add_column_sql end
Begins a transaction.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 640 def begin_db_transaction execute "BEGIN" end
Changes the column of a table.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 927 def change_column(table_name, column_name, type, options = {}) clear_cache! quoted_table_name = quote_table_name(table_name) execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" change_column_default(table_name, column_name, options[:default]) if options_include_default?(options) change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null) end
Changes the default value of a table column.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 938 def change_column_default(table_name, column_name, default) clear_cache! execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}" end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 943 def change_column_null(table_name, column_name, null, default = nil) clear_cache! unless null || default.nil? execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL") end execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL") end
Clears the prepared statements cache.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 328 def clear_cache! @statements.clear end
Returns the current client message level.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 827 def client_min_messages query('SHOW client_min_messages', 'SCHEMA')[0][0] end
Set the client message level.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 832 def client_min_messages=(level) execute("SET client_min_messages TO '#{level}'", 'SCHEMA') end
Returns the list of all column definitions for a table.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 789 def columns(table_name, name = nil) # Limit, precision, and scale are all handled by the superclass. column_definitions(table_name).collect do |column_name, type, default, notnull| PostgreSQLColumn.new(column_name, default, type, notnull == 'f') end end
Commits a transaction.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 645 def commit_db_transaction execute "COMMIT" end
Create a new PostgreSQL database. Options include :owner
,
:template
, :encoding
, :tablespace
,
and :connection_limit
(note that MySQL uses
:charset
while PostgreSQL uses :encoding
).
Example:
create_database config[:database], config create_database 'foo_development', :encoding => 'unicode'
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 684 def create_database(name, options = {}) options = options.reverse_merge(:encoding => "utf8") option_string = options.symbolize_keys.sum do |key, value| case key when :owner " OWNER = \"#{value}\"" when :template " TEMPLATE = \"#{value}\"" when :encoding " ENCODING = '#{value}'" when :tablespace " TABLESPACE = \"#{value}\"" when :connection_limit " CONNECTION LIMIT = #{value}" else "" end end execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 658 def create_savepoint execute("SAVEPOINT #{current_savepoint_name}") end
Returns the current database name.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 797 def current_database query('select current_database()')[0][0] end
Disconnects from the database if already connected. Otherwise, this method does nothing.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 353 def disconnect! clear_cache! @connection.close rescue nil end
Returns the current database encoding format.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 802 def encoding query(" SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database WHERE pg_database.datname LIKE '#{current_database}' ")[0][0] end
Escapes binary strings for bytea input to the database.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 401 def escape_bytea(value) @connection.escape_bytea(value) if value end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 611 def exec_delete(sql, name = 'SQL', binds = []) log(sql, name, binds) do result = binds.empty? ? exec_no_cache(sql, binds) : exec_cache(sql, binds) affected = result.cmd_tuples result.clear affected end end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 600 def exec_query(sql, name = 'SQL', binds = []) log(sql, name, binds) do result = binds.empty? ? exec_no_cache(sql, binds) : exec_cache(sql, binds) ret = ActiveRecord::Result.new(result.fields, result_as_array(result)) result.clear return ret end end
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 590 def execute(sql, name = nil) log(sql, name) do @connection.async_exec(sql) end end
Extracts the table and schema name from name
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 740 def extract_schema_and_table(name) schema, table = name.split('.', 2) unless table # A table was provided without a schema table = schema schema = nil end if name =~ /^"/ # Handle quoted table names table = name schema = nil end [schema, table] end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 965 def index_name_length 63 end
Returns an array of indexes for the given table.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 756 def indexes(table_name, name = nil) result = query(" SELECT distinct i.relname, d.indisunique, d.indkey, t.oid FROM pg_class t INNER JOIN pg_index d ON t.oid = d.indrelid INNER JOIN pg_class i ON d.indexrelid = i.oid WHERE i.relkind = 'i' AND d.indisprimary = 'f' AND t.relname = '#{table_name}' AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) ) ORDER BY i.relname ", name) result.map do |row| index_name = row[0] unique = row[1] == 't' indkey = row[2].split(" ") oid = row[3] columns = Hash[query(" SELECT a.attnum, a.attname FROM pg_attribute a WHERE a.attrelid = #{oid} AND a.attnum IN (#{indkey.join(",")}) ", "Columns for index #{row[0]} on #{table_name}")] column_names = columns.values_at(*indkey).compact column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names) end.compact end
Executes an INSERT query and returns the new record’s ID
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 524 def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) # Extract the table from the insert sql. Yuck. _, table = extract_schema_and_table(sql.split(" ", 4)[2]) pk ||= primary_key(table) if pk select_value("#{sql} RETURNING #{quote_column_name(pk)}") else super end end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 654 def outside_transaction? @connection.transaction_status == PGconn::PQTRANS_IDLE end
Returns just a table’s primary key
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 894 def primary_key(table) row = exec_query(" SELECT DISTINCT(attr.attname) FROM pg_attribute attr INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND dep.refobjid = $1::regclass ", 'SCHEMA', [[nil, table]]).rows.first row && row.first end
Checks the following cases:
-
table_name
-
“table.name”
-
schema_name.table_name
-
schema_name.“table.name”
-
“schema.name”.table_name
-
“schema.name”.“table.name”
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 466 def quote_table_name(name) schema, name_part = extract_pg_identifier_from_name(name.to_s) unless name_part quote_column_name(schema) else table_name, name_part = extract_pg_identifier_from_name(name_part) "#{quote_column_name(schema)}.#{quote_column_name(table_name)}" end end
Close then reopen the connection.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 340 def reconnect! clear_cache! @connection.reset configure_connection end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 666 def release_savepoint execute("RELEASE SAVEPOINT #{current_savepoint_name}") end
Renames a column in a table.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 952 def rename_column(table_name, column_name, new_column_name) clear_cache! execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 961 def rename_index(table_name, old_name, new_name) execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}" end
Renames a table.
Example:
rename_table('octopuses', 'octopi')
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 911 def rename_table(name, new_name) clear_cache! execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 346 def reset! clear_cache! super end
Aborts a transaction.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 650 def rollback_db_transaction execute "ROLLBACK" end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 662 def rollback_to_savepoint execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}") end
Returns the active schema search path.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 822 def schema_search_path @schema_search_path ||= query('SHOW search_path')[0][0] end
Sets the schema search path to a string of comma-separated schema names. Names beginning with $ have to be quoted (e.g. $user => ‘$user’). See: www.postgresql.org/docs/current/static/ddl-schemas.html
This should be not be called manually but set in database.yml.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 814 def schema_search_path=(schema_csv) if schema_csv execute "SET search_path TO #{schema_csv}" @schema_search_path = schema_csv end end
Executes a SELECT query and returns an array of rows. Each row is an array of field values.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 519 def select_rows(sql, name = nil) select_raw(sql, name).last end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 843 def serial_sequence(table, column) result = exec_query(" SELECT pg_get_serial_sequence($1, $2) ", 'SCHEMA', [[nil, table], [nil, column]]) result.rows.first.first end
Set the authorized user for this session
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 493 def session_auth=(user) clear_cache! exec_query "SET SESSION AUTHORIZATION #{user}" end
Enable standard-conforming strings if available.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 373 def set_standard_conforming_strings old, self.client_min_messages = client_min_messages, 'panic' execute('SET standard_conforming_strings = on', 'SCHEMA') rescue nil ensure self.client_min_messages = old end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 622 def sql_for_insert(sql, pk, id_value, sequence_name, binds) unless pk _, table = extract_schema_and_table(sql.split(" ", 4)[2]) pk = primary_key(table) end sql = "#{sql} RETURNING #{quote_column_name(pk)}" if pk [sql, binds] end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 596 def substitute_at(column, index) Arel.sql("$#{index + 1}") end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 384 def supports_ddl_transactions? true end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 380 def supports_insert_with_returning? true end
Returns true, since this connection adapter supports migrations.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 363 def supports_migrations? true end
Returns true, since this connection adapter supports savepoints.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 389 def supports_savepoints? true end
Returns true
, since this connection adapter supports prepared
statement caching.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 247 def supports_statement_cache? true end
Returns the configured supported identifier length supported by PostgreSQL
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 394 def table_alias_length @table_alias_length ||= query('SHOW max_identifier_length')[0][0].to_i end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 724 def table_exists?(name) schema, table = extract_schema_and_table(name.to_s) return false unless table # Abstract classes is having nil table name binds = [[nil, table.gsub(/(^"|"$)/,'')]] binds << [nil, schema] if schema exec_query(" SELECT COUNT(*) FROM pg_tables WHERE tablename = $1 AND schemaname = #{schema ? "$2" : "ANY (current_schemas(false))"} ", 'SCHEMA', binds).rows.first[0].to_i > 0 end
Returns the list of all tables in the schema search path or a specified schema.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 716 def tables(name = nil) query(" SELECT tablename FROM pg_tables WHERE schemaname = ANY (current_schemas(false)) ", 'SCHEMA').map { |row| row[0] } end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 441 def type_cast(value, column) return super unless column case value when String return super unless 'bytea' == column.sql_type { :value => value, :format => 1 } else super end end
Maps logical Rails types to PostgreSQL-specific data types.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 970 def type_to_sql(type, limit = nil, precision = nil, scale = nil) return super unless type.to_s == 'integer' return 'integer' unless limit case limit when 1, 2; 'smallint' when 3, 4; 'integer' when 5..8; 'bigint' else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.") end end
Unescapes bytea output from a database to the binary string it represents. NOTE: This is NOT an inverse of #escape_bytea! This is only to be used
on escaped binary output from database drive.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 408 def unescape_bytea(value) @connection.unescape_bytea(value) if value end
Executes an UPDATE query and returns the number of affected tuples.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 635 def update_sql(sql, name = nil) super.cmd_tuples end
Returns the version of the connected PostgreSQL server.
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 1002 def postgresql_version @connection.server_version end
Source: show
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 1006 def translate_exception(exception, message) case exception.message when /duplicate key value violates unique constraint/ RecordNotUnique.new(message, exception) when /violates foreign key constraint/ InvalidForeignKey.new(message, exception) else super end end