module ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements

Public Instance Methods

client_min_messages() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 326
def client_min_messages
  select_value('SHOW client_min_messages', 'SCHEMA')
end

Returns the current client message level.

client_min_messages=(level) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 331
def client_min_messages=(level)
  execute("SET client_min_messages TO '#{level}'", 'SCHEMA')
end

Set the client message level.

collation() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 278
def collation
  select_value("SELECT datcollate FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA')
end

Returns the current database collation.

create_database(name, options = {}) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 38
def create_database(name, options = {})
  options = { encoding: 'utf8' }.merge!(options.symbolize_keys)

  option_string = options.inject("") do |memo, (key, value)|
    memo += case key
    when :owner
      " OWNER = \"#{value}\""
    when :template
      " TEMPLATE = \"#{value}\""
    when :encoding
      " ENCODING = '#{value}'"
    when :collation
      " LC_COLLATE = '#{value}'"
    when :ctype
      " LC_CTYPE = '#{value}'"
    when :tablespace
      " TABLESPACE = \"#{value}\""
    when :connection_limit
      " CONNECTION LIMIT = #{value}"
    else
      ""
    end
  end

  execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}"
end

Create a new PostgreSQL database. Options include :owner, :template, :encoding (defaults to utf8), :collation, :ctype, :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'
create_schema(schema_name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 299
def create_schema schema_name
  execute "CREATE SCHEMA #{quote_schema_name(schema_name)}"
end

Creates a schema for the given schema name.

ctype() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 283
def ctype
  select_value("SELECT datctype FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA')
end

Returns the current database ctype.

current_database() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 263
def current_database
  select_value('select current_database()', 'SCHEMA')
end

Returns the current database name.

current_schema() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 268
def current_schema
  select_value('SELECT current_schema', 'SCHEMA')
end

Returns the current schema name.

data_source_exists?(name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 107
        def data_source_exists?(name)
          name = Utils.extract_schema_qualified_name(name.to_s)
          return false unless name.identifier

          select_value("              SELECT COUNT(*)
              FROM pg_class c
              LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relkind IN ('r','v','m') -- (r)elation/table, (v)iew, (m)aterialized view
              AND c.relname = '#{name.identifier}'
              AND n.nspname = #{name.schema ? "'#{name.schema}'" : 'ANY (current_schemas(false))'}
", 'SCHEMA').to_i > 0
        end
data_sources() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 84
        def data_sources # :nodoc
          select_values("            SELECT c.relname
            FROM pg_class c
            LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v','m') -- (r)elation/table, (v)iew, (m)aterialized view
            AND n.nspname = ANY (current_schemas(false))
", 'SCHEMA')
        end
drop_schema(schema_name, options = {}) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 304
def drop_schema(schema_name, options = {})
  execute "DROP SCHEMA#{' IF EXISTS' if options[:if_exists]} #{quote_schema_name(schema_name)} CASCADE"
end

Drops the schema for the given schema name.

encoding() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 273
def encoding
  select_value("SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA')
end

Returns the current database encoding format.

fetch_type_metadata(column_name, sql_type, oid, fmod) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 678
def fetch_type_metadata(column_name, sql_type, oid, fmod)
  cast_type = get_oid_type(oid, fmod, column_name, sql_type)
  simple_type = SqlTypeMetadata.new(
    sql_type: sql_type,
    type: cast_type.type,
    limit: cast_type.limit,
    precision: cast_type.precision,
    scale: cast_type.scale,
  )
  PostgreSQLTypeMetadata.new(simple_type, oid: oid, fmod: fmod)
end
foreign_keys(table_name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 595
        def foreign_keys(table_name)
          fk_info = select_all("            SELECT t2.oid::regclass::text AS to_table, a1.attname AS column, a2.attname AS primary_key, c.conname AS name, c.confupdtype AS on_update, c.confdeltype AS on_delete
            FROM pg_constraint c
            JOIN pg_class t1 ON c.conrelid = t1.oid
            JOIN pg_class t2 ON c.confrelid = t2.oid
            JOIN pg_attribute a1 ON a1.attnum = c.conkey[1] AND a1.attrelid = t1.oid
            JOIN pg_attribute a2 ON a2.attnum = c.confkey[1] AND a2.attrelid = t2.oid
            JOIN pg_namespace t3 ON c.connamespace = t3.oid
            WHERE c.contype = 'f'
              AND t1.relname = #{quote(table_name)}
              AND t3.nspname = ANY (current_schemas(false))
            ORDER BY c.conname
".strip_heredoc, 'SCHEMA')

          fk_info.map do |row|
            options = {
              column: row['column'],
              name: row['name'],
              primary_key: row['primary_key']
            }

            options[:on_delete] = extract_foreign_key_action(row['on_delete'])
            options[:on_update] = extract_foreign_key_action(row['on_update'])

            ForeignKeyDefinition.new(table_name, row['to_table'], options)
          end
        end
index_name_exists?(table_name, index_name, default) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 155
        def index_name_exists?(table_name, index_name, default)
          table = Utils.extract_schema_qualified_name(table_name.to_s)
          index = Utils.extract_schema_qualified_name(index_name.to_s)

          select_value("            SELECT COUNT(*)
            FROM pg_class t
            INNER JOIN pg_index d ON t.oid = d.indrelid
            INNER JOIN pg_class i ON d.indexrelid = i.oid
            LEFT JOIN pg_namespace n ON n.oid = i.relnamespace
            WHERE i.relkind = 'i'
              AND i.relname = '#{index.identifier}'
              AND t.relname = '#{table.identifier}'
              AND n.nspname = #{index.schema ? "'#{index.schema}'" : 'ANY (current_schemas(false))'}
", 'SCHEMA').to_i > 0
        end

Verifies existence of an index with a given name.

indexes(table_name, name = nil) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 173
        def indexes(table_name, name = nil)
          table = Utils.extract_schema_qualified_name(table_name.to_s)

          result = query("            SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid,
                            pg_catalog.obj_description(i.oid, 'pg_class') AS comment,
            (SELECT COUNT(*) FROM pg_opclass o
               JOIN (SELECT unnest(string_to_array(d.indclass::text, ' '))::int oid) c
                 ON o.oid = c.oid WHERE o.opcdefault = 'f')
            FROM pg_class t
            INNER JOIN pg_index d ON t.oid = d.indrelid
            INNER JOIN pg_class i ON d.indexrelid = i.oid
            LEFT JOIN pg_namespace n ON n.oid = i.relnamespace
            WHERE i.relkind = 'i'
              AND d.indisprimary = 'f'
              AND t.relname = '#{table.identifier}'
              AND n.nspname = #{table.schema ? "'#{table.schema}'" : 'ANY (current_schemas(false))'}
            ORDER BY i.relname
", 'SCHEMA')

          result.map do |row|
            index_name = row[0]
            unique = row[1]
            indkey = row[2].split(" ").map(&:to_i)
            inddef = row[3]
            oid = row[4]
            comment = row[5]
            opclass = row[6]

            using, expressions, where = inddef.scan(/ USING (\w+?) \((.+?)\)(?: WHERE (.+))?\z/).flatten

            if indkey.include?(0) || opclass > 0
              columns = expressions
            else
              columns = Hash[query("                SELECT a.attnum, a.attname
                FROM pg_attribute a
                WHERE a.attrelid = #{oid}
                AND a.attnum IN (#{indkey.join(",")})
".strip_heredoc, "SCHEMA")].values_at(*indkey).compact

              # add info on sort order for columns (only desc order is explicitly specified, asc is the default)
              orders = Hash[
                expressions.scan(/(\w+) DESC/).flatten.map { |order_column| [order_column, :desc] }
              ]
            end

            IndexDefinition.new(table_name, index_name, unique, columns, [], orders, where, nil, using.to_sym, comment.presence)
          end.compact
        end

Returns an array of indexes for the given table.

rename_index(table_name, old_name, new_name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 589
def rename_index(table_name, old_name, new_name)
  validate_index_length!(table_name, new_name)

  execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}"
end

Renames an index of a table. Raises error if length of new index name is greater than allowed limit.

rename_table(table_name, new_name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 468
def rename_table(table_name, new_name)
  clear_cache!
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}"
  pk, seq = pk_and_sequence_for(new_name)
  if seq && seq.identifier == "#{table_name}_#{pk}_seq"
    new_seq = "#{new_name}_#{pk}_seq"
    idx = "#{table_name}_pkey"
    new_idx = "#{new_name}_pkey"
    execute "ALTER TABLE #{seq.quoted} RENAME TO #{quote_table_name(new_seq)}"
    execute "ALTER INDEX #{quote_table_name(idx)} RENAME TO #{quote_table_name(new_idx)}"
  end

  rename_table_indexes(table_name, new_name)
end

Renames a table. Also renames a table's primary key sequence if the sequence name exists and matches the Active Record default.

Example:

rename_table('octopuses', 'octopi')
schema_exists?(name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 150
def schema_exists?(name)
  select_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = '#{name}'", 'SCHEMA').to_i > 0
end

Returns true if schema exists.

schema_names() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 288
        def schema_names
          select_values("            SELECT nspname
              FROM pg_namespace
             WHERE nspname !~ '^pg_.*'
               AND nspname NOT IN ('information_schema')
             ORDER by nspname;
", 'SCHEMA')
        end

Returns an array of schema names.

schema_search_path() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 321
def schema_search_path
  @schema_search_path ||= select_value('SHOW search_path', 'SCHEMA')
end

Returns the active schema search path.

schema_search_path=(schema_csv) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 313
def schema_search_path=(schema_csv)
  if schema_csv
    execute("SET search_path TO #{schema_csv}", 'SCHEMA')
    @schema_search_path = schema_csv
  end
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.

serial_sequence(table, column) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 344
def serial_sequence(table, column)
  select_value("SELECT pg_get_serial_sequence('#{table}', '#{column}')", 'SCHEMA')
end
table_exists?(name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 97
        def table_exists?(name)
          ActiveSupport::Deprecation.warn("            #table_exists? currently checks both tables and views.
            This behavior is deprecated and will be changed with Rails 5.1 to only check tables.
            Use #data_source_exists? instead.
".squish)

          data_source_exists?(name)
        end

Returns true if table exists. If the schema is not specified as part of name then it will only find tables within the current schema search path (regardless of permissions to access tables in other schemas)

tables(name = nil) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 74
        def tables(name = nil)
          if name
            ActiveSupport::Deprecation.warn("              Passing arguments to #tables is deprecated without replacement.
".squish)
          end

          select_values("SELECT tablename FROM pg_tables WHERE schemaname = ANY(current_schemas(false))", 'SCHEMA')
        end

Returns the list of all tables in the schema search path.

type_to_sql(type, limit = nil, precision = nil, scale = nil, array = nil) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 633
def type_to_sql(type, limit = nil, precision = nil, scale = nil, array = nil)
  sql = case type.to_s
  when 'binary'
    # PostgreSQL doesn't support limits on binary (bytea) columns.
    # The hard limit is 1GB, because of a 32-bit size field, and TOAST.
    case limit
    when nil, 0..0x3fffffff; super(type)
    else raise(ActiveRecordError, "No binary type has byte size #{limit}.")
    end
  when 'text'
    # PostgreSQL doesn't support limits on text columns.
    # The hard limit is 1GB, according to section 8.3 in the manual.
    case limit
    when nil, 0..0x3fffffff; super(type)
    else raise(ActiveRecordError, "The limit on text can be at most 1GB - 1byte.")
    end
  when 'integer'
    case limit
    when 1, 2; 'smallint'
    when nil, 3, 4; 'integer'
    when 5..8; 'bigint'
    else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with scale 0 instead.")
    end
  else
    super(type, limit, precision, scale)
  end

  sql << '[]' if array && type != :primary_key
  sql
end

Maps logical Rails types to PostgreSQL-specific data types.

Calls superclass method

© 2004–2018 David Heinemeier Hansson
Licensed under the MIT License.