Files

Class Index [+]

Quicksearch

ActiveRecord::ConnectionAdapters::PostgreSQLAdapter

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:

Constants

ADAPTER_NAME
NATIVE_DATABASE_TYPES

Public Class Methods

new(connection, logger, connection_parameters, config) click to toggle source

Initializes and connects a PostgreSQL adapter.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 219
219:       def initialize(connection, logger, connection_parameters, config)
220:         super(connection, logger)
221:         @connection_parameters, @config = connection_parameters, config
222: 
223:         # @local_tz is initialized as nil to avoid warnings when connect tries to use it
224:         @local_tz = nil
225:         @table_alias_length = nil
226:         @postgresql_version = nil
227: 
228:         connect
229:         @local_tz = execute('SHOW TIME ZONE').first["TimeZone"]
230:       end

Public Instance Methods

active?() click to toggle source

Is this connection alive and ready for queries?

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 233
233:       def active?
234:         if @connection.respond_to?(:status)
235:           @connection.status == PGconn::CONNECTION_OK
236:         else
237:           # We're asking the driver, not Active Record, so use @connection.query instead of #query
238:           @connection.query 'SELECT 1'
239:           true
240:         end
241:       # postgres-pr raises a NoMethodError when querying if no connection is available.
242:       rescue PGError, NoMethodError
243:         false
244:       end
adapter_name() click to toggle source

Returns ‘PostgreSQL’ as adapter name for identification purposes.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 214
214:       def adapter_name
215:         ADAPTER_NAME
216:       end
add_column(table_name, column_name, type, options = {}) click to toggle source

Adds a new column to the named table. See TableDefinition#column for details of the options you can use.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 795
795:       def add_column(table_name, column_name, type, options = {})
796:         default = options[:default]
797:         notnull = options[:null] == false
798: 
799:         # Add the column.
800:         execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}")
801: 
802:         change_column_default(table_name, column_name, default) if options_include_default?(options)
803:         change_column_null(table_name, column_name, false, default) if notnull
804:       end
begin_db_transaction() click to toggle source

Begins a transaction.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 511
511:       def begin_db_transaction
512:         execute "BEGIN"
513:       end
change_column(table_name, column_name, type, options = {}) click to toggle source

Changes the column of a table.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 807
807:       def change_column(table_name, column_name, type, options = {})
808:         quoted_table_name = quote_table_name(table_name)
809: 
810:         begin
811:           execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
812:         rescue ActiveRecord::StatementInvalid => e
813:           raise e if postgresql_version > 80000
814:           # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it.
815:           begin
816:             begin_db_transaction
817:             tmp_column_name = "#{column_name}_ar_tmp"
818:             add_column(table_name, tmp_column_name, type, options)
819:             execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{type_to_sql(type, options[:limit], options[:precision], options[:scale])})"
820:             remove_column(table_name, column_name)
821:             rename_column(table_name, tmp_column_name, column_name)
822:             commit_db_transaction
823:           rescue
824:             rollback_db_transaction
825:           end
826:         end
827: 
828:         change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
829:         change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
830:       end
change_column_default(table_name, column_name, default) click to toggle source

Changes the default value of a table column.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 833
833:       def change_column_default(table_name, column_name, default)
834:         execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}"
835:       end
change_column_null(table_name, column_name, null, default = nil) click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 837
837:       def change_column_null(table_name, column_name, null, default = nil)
838:         unless null || default.nil?
839:           execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
840:         end
841:         execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
842:       end
client_min_messages() click to toggle source

Returns the current client message level.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 698
698:       def client_min_messages
699:         query('SHOW client_min_messages')[0][0]
700:       end
client_min_messages=(level) click to toggle source

Set the client message level.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 703
703:       def client_min_messages=(level)
704:         execute("SET client_min_messages TO '#{level}'")
705:       end
columns(table_name, name = nil) click to toggle source

Returns the list of all column definitions for a table.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 660
660:       def columns(table_name, name = nil)
661:         # Limit, precision, and scale are all handled by the superclass.
662:         column_definitions(table_name).collect do |name, type, default, notnull|
663:           PostgreSQLColumn.new(name, default, type, notnull == 'f')
664:         end
665:       end
commit_db_transaction() click to toggle source

Commits a transaction.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 516
516:       def commit_db_transaction
517:         execute "COMMIT"
518:       end
create(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) click to toggle source
Alias for: insert
create_database(name, options = {}) click to toggle source

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'
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 555
555:       def create_database(name, options = {})
556:         options = options.reverse_merge(:encoding => "utf8")
557: 
558:         option_string = options.symbolize_keys.sum do |key, value|
559:           case key
560:           when :owner
561:             " OWNER = \"#{value}\""
562:           when :template
563:             " TEMPLATE = \"#{value}\""
564:           when :encoding
565:             " ENCODING = '#{value}'"
566:           when :tablespace
567:             " TABLESPACE = \"#{value}\""
568:           when :connection_limit
569:             " CONNECTION LIMIT = #{value}"
570:           else
571:             ""
572:           end
573:         end
574: 
575:         execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}"
576:       end
create_savepoint() click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 529
529:       def create_savepoint
530:         execute("SAVEPOINT #{current_savepoint_name}")
531:       end
current_database() click to toggle source

Returns the current database name.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 668
668:       def current_database
669:         query('select current_database()')[0][0]
670:       end
disconnect!() click to toggle source

Close the connection.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 258
258:       def disconnect!
259:         @connection.close rescue nil
260:       end
encoding() click to toggle source

Returns the current database encoding format.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 673
673:       def encoding
674:         query(          SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database          WHERE pg_database.datname LIKE '#{current_database}')[0][0]
675:       end
escape_bytea(value) click to toggle source

Escapes binary strings for bytea input to the database.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 305
305:       def escape_bytea(value)
306:         @connection.escape_bytea(value) if value
307:       end
execute(sql, name = nil) click to toggle source

Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 495
495:       def execute(sql, name = nil)
496:         log(sql, name) do
497:           if @async
498:             @connection.async_exec(sql)
499:           else
500:             @connection.exec(sql)
501:           end
502:         end
503:       end
index_name_length() click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 853
853:       def index_name_length
854:         63
855:       end
indexes(table_name, name = nil) click to toggle source

Returns the list of all indexes for a table.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 626
626:       def indexes(table_name, name = nil)
627:          schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
628:          result = query(           SELECT distinct i.relname, d.indisunique, d.indkey, t.oid             FROM pg_class t, pg_class i, pg_index d           WHERE i.relkind = 'i'             AND d.indexrelid = i.oid             AND d.indisprimary = 'f'             AND t.oid = d.indrelid             AND t.relname = '#{table_name}'             AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (#{schemas}) )          ORDER BY i.relname, name)
629: 
630: 
631:         result.map do |row|
632:           index_name = row[0]
633:           unique = row[1] == 't'
634:           indkey = row[2].split(" ")
635:           oid = row[3]
636: 
637:           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}")]
638: 
639:           column_names = columns.values_at(*indkey).compact
640:           column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names)
641:         end.compact
642:       end
insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) click to toggle source

Executes an INSERT query and returns the new record’s ID

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 407
407:       def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
408:         # Extract the table from the insert sql. Yuck.
409:         table = sql.split(" ", 4)[2].gsub('"', '')
410: 
411:         # Try an insert with 'returning id' if available (PG >= 8.2)
412:         if supports_insert_with_returning?
413:           pk, sequence_name = *pk_and_sequence_for(table) unless pk
414:           if pk
415:             id = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
416:             clear_query_cache
417:             return id
418:           end
419:         end
420: 
421:         # Otherwise, insert then grab last_insert_id.
422:         if insert_id = super
423:           insert_id
424:         else
425:           # If neither pk nor sequence name is given, look them up.
426:           unless pk || sequence_name
427:             pk, sequence_name = *pk_and_sequence_for(table)
428:           end
429: 
430:           # If a pk is given, fallback to default sequence name.
431:           # Don't fetch last insert id for a table without a pk.
432:           if pk && sequence_name ||= default_sequence_name(table, pk)
433:             last_insert_id(table, sequence_name)
434:           end
435:         end
436:       end
Also aliased as: create
outside_transaction?() click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 525
525:       def outside_transaction?
526:         @connection.transaction_status == PGconn::PQTRANS_IDLE
527:       end
primary_key(table) click to toggle source

Returns just a table’s primary key

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 783
783:       def primary_key(table)
784:         pk_and_sequence = pk_and_sequence_for(table)
785:         pk_and_sequence && pk_and_sequence.first
786:       end
quote_table_name(name) click to toggle source

Checks the following cases:

  • table_name

  • “table.name“

  • schema_name.table_name

  • schema_name.“table.name“

  • “schema.name“.table_name

  • “schema.name“.“table.name“

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 352
352:       def quote_table_name(name)
353:         schema, name_part = extract_pg_identifier_from_name(name.to_s)
354: 
355:         unless name_part
356:           quote_column_name(schema)
357:         else
358:           table_name, name_part = extract_pg_identifier_from_name(name_part)
359:           "#{quote_column_name(schema)}.#{quote_column_name(table_name)}"
360:         end
361:       end
reconnect!() click to toggle source

Close then reopen the connection.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 247
247:       def reconnect!
248:         if @connection.respond_to?(:reset)
249:           @connection.reset
250:           configure_connection
251:         else
252:           disconnect!
253:           connect
254:         end
255:       end
release_savepoint() click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 537
537:       def release_savepoint
538:         execute("RELEASE SAVEPOINT #{current_savepoint_name}")
539:       end
rename_column(table_name, column_name, new_column_name) click to toggle source

Renames a column in a table.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 845
845:       def rename_column(table_name, column_name, new_column_name)
846:         execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
847:       end
rename_table(name, new_name) click to toggle source

Renames a table.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 789
789:       def rename_table(name, new_name)
790:         execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}"
791:       end
rollback_db_transaction() click to toggle source

Aborts a transaction.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 521
521:       def rollback_db_transaction
522:         execute "ROLLBACK"
523:       end
rollback_to_savepoint() click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 533
533:       def rollback_to_savepoint
534:         execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}")
535:       end
schema_search_path() click to toggle source

Returns the active schema search path.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 693
693:       def schema_search_path
694:         @schema_search_path ||= query('SHOW search_path')[0][0]
695:       end
schema_search_path=(schema_csv) click to toggle source

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.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 685
685:       def schema_search_path=(schema_csv)
686:         if schema_csv
687:           execute "SET search_path TO #{schema_csv}"
688:           @schema_search_path = schema_csv
689:         end
690:       end
select_rows(sql, name = nil) click to toggle source

Executes a SELECT query and returns an array of rows. Each row is an array of field values.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 402
402:       def select_rows(sql, name = nil)
403:         select_raw(sql, name).last
404:       end
set_standard_conforming_strings() click to toggle source

Enable standard-conforming strings if available.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 277
277:       def set_standard_conforming_strings
278:         old, self.client_min_messages = client_min_messages, 'panic'
279:         execute('SET standard_conforming_strings = on') rescue nil
280:       ensure
281:         self.client_min_messages = old
282:       end
supports_ddl_transactions?() click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 288
288:       def supports_ddl_transactions?
289:         true
290:       end
supports_insert_with_returning?() click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 284
284:       def supports_insert_with_returning?
285:         postgresql_version >= 80200
286:       end
supports_migrations?() click to toggle source

Does PostgreSQL support migrations?

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 267
267:       def supports_migrations?
268:         true
269:       end
supports_savepoints?() click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 292
292:       def supports_savepoints?
293:         true
294:       end
table_alias_length() click to toggle source

Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 298
298:       def table_alias_length
299:         @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63)
300:       end
table_exists?(name) click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 603
603:       def table_exists?(name)
604:         name          = name.to_s
605:         schema, table = name.split('.', 2)
606: 
607:         unless table # A table was provided without a schema
608:           table  = schema
609:           schema = nil
610:         end
611: 
612:         if name =~ /^"/ # Handle quoted table names
613:           table  = name
614:           schema = nil
615:         end
616: 
617:         query(            SELECT COUNT(*)            FROM pg_tables            WHERE tablename = '#{table.gsub(/(^"|"$)/,'')}'            #{schema ? "AND schemaname = '#{schema}'" : ''}).first[0].to_i > 0
618:       end
tables(name = nil) click to toggle source

Returns the list of all tables in the schema search path or a specified schema.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 595
595:       def tables(name = nil)
596:         query(          SELECT tablename          FROM pg_tables          WHERE schemaname = ANY (current_schemas(false)), name).map { |row| row[0] }
597:       end
type_to_sql(type, limit = nil, precision = nil, scale = nil) click to toggle source

Maps logical Rails types to PostgreSQL-specific data types.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 858
858:       def type_to_sql(type, limit = nil, precision = nil, scale = nil)
859:         return super unless type.to_s == 'integer'
860:         return 'integer' unless limit
861: 
862:         case limit
863:           when 1, 2; 'smallint'
864:           when 3, 4; 'integer'
865:           when 5..8; 'bigint'
866:           else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
867:         end
868:       end
unescape_bytea(value) click to toggle source

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.
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 312
312:       def unescape_bytea(value)
313:         @connection.unescape_bytea(value) if value
314:       end
update_sql(sql, name = nil) click to toggle source

Executes an UPDATE query and returns the number of affected tuples.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 506
506:       def update_sql(sql, name = nil)
507:         super.cmd_tuples
508:       end

Protected Instance Methods

postgresql_version() click to toggle source

Returns the version of the connected PostgreSQL version.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 893
893:         def postgresql_version
894:           @postgresql_version ||=
895:             if @connection.respond_to?(:server_version)
896:               @connection.server_version
897:             else
898:               # Mimic PGconn.server_version behavior
899:               begin
900:                 query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/
901:                 ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i
902:               rescue
903:                 0
904:               end
905:             end
906:         end
translate_exception(exception, message) click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 908
908:         def translate_exception(exception, message)
909:           case exception.message
910:           when /duplicate key value violates unique constraint/
911:             RecordNotUnique.new(message, exception)
912:           when /violates foreign key constraint/
913:             InvalidForeignKey.new(message, exception)
914:           else
915:             super
916:           end
917:         end

Private Instance Methods

configure_connection() click to toggle source

Configures the encoding, verbosity, schema search path, and time zone of the connection. This is called by # and should not be called manually.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 944
944:         def configure_connection
945:           if @config[:encoding]
946:             if @connection.respond_to?(:set_client_encoding)
947:               @connection.set_client_encoding(@config[:encoding])
948:             else
949:               execute("SET client_encoding TO '#{@config[:encoding]}'")
950:             end
951:           end
952:           self.client_min_messages = @config[:min_messages] if @config[:min_messages]
953:           self.schema_search_path = @config[:schema_search_path] || @config[:schema_order]
954: 
955:           # Use standard-conforming strings if available so we don't have to do the E'...' dance.
956:           set_standard_conforming_strings
957: 
958:           # If using Active Record's time zone support configure the connection to return
959:           # TIMESTAMP WITH ZONE types in UTC.
960:           if ActiveRecord::Base.default_timezone == :utc
961:             execute("SET time zone 'UTC'")
962:           elsif @local_tz
963:             execute("SET time zone '#{@local_tz}'")
964:           end
965:         end
connect() click to toggle source

Connects to a PostgreSQL server and sets up the adapter depending on the connected server’s characteristics.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 927
927:         def connect
928:           @connection = PGconn.connect(*@connection_parameters)
929:           PGconn.translate_results = false if PGconn.respond_to?(:translate_results=)
930: 
931:           # Ignore async_exec and async_query when using postgres-pr.
932:           @async = @config[:allow_concurrency] && @connection.respond_to?(:async_exec)
933: 
934:           # Money type has a fixed precision of 10 in PostgreSQL 8.2 and below, and as of
935:           # PostgreSQL 8.3 it has a fixed precision of 19. PostgreSQLColumn.extract_precision
936:           # should know about this but can't detect it there, so deal with it here.
937:           PostgreSQLColumn.money_precision = (postgresql_version >= 80300) ? 19 : 10
938: 
939:           configure_connection
940:         end
extract_pg_identifier_from_name(name) click to toggle source
      # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 1018
1018:         def extract_pg_identifier_from_name(name)
1019:           match_data = name[0,1] == '"' ? name.match(/\"([^\"]+)\"/) : name.match(/([^\.]+)/)
1020: 
1021:           if match_data
1022:             rest = name[match_data[0].length..1]
1023:             rest = rest[1..1] if rest[0,1] == "."
1024:             [match_data[1], (rest.length > 0 ? rest : nil)]
1025:           end
1026:         end
select(sql, name = nil) click to toggle source

Executes a SELECT query and returns the results, performing any data type conversions that are required to be performed here instead of in PostgreSQLColumn.

     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 974
974:         def select(sql, name = nil)
975:           fields, rows = select_raw(sql, name)
976:           rows.map do |row|
977:             Hash[*fields.zip(row).flatten]
978:           end
979:         end
select_raw(sql, name = nil) click to toggle source
     # File lib/active_record/connection_adapters/postgresql_adapter.rb, line 981
981:         def select_raw(sql, name = nil)
982:           res = execute(sql, name)
983:           results = result_as_array(res)
984:           fields = res.fields
985:           res.clear
986:           return fields, results
987:         end

Disabled; run with --debug to generate this.

[Validate]

Generated with the Darkfish Rdoc Generator 1.1.6.