- A
- B
- C
- D
- E
- F
- I
- N
- Q
- R
- S
- V
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 740 def add_index_opclass(quoted_columns, **options) opclasses = options_for_index_columns(options[:opclass]) quoted_columns.each do |name, column| column << " #{opclasses[name]}" if opclasses[name].present? end end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 732 def add_timestamps_for_alter(table_name, options = {}) [add_column_for_alter(table_name, :created_at, :datetime, options), add_column_for_alter(table_name, :updated_at, :datetime, options)] end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 371 def bulk_change_table(table_name, operations) sql_fragments = [] non_combinable_operations = [] operations.each do |command, args| table, arguments = args.shift, args method = :"#{command}_for_alter" if respond_to?(method, true) sqls, procs = Array(send(method, table, *arguments)).partition { |v| v.is_a?(String) } sql_fragments << sqls non_combinable_operations.concat(procs) else execute "ALTER TABLE #{quote_table_name(table_name)} #{sql_fragments.join(", ")}" unless sql_fragments.empty? non_combinable_operations.each(&:call) sql_fragments = [] non_combinable_operations = [] send(command, table, *arguments) end end execute "ALTER TABLE #{quote_table_name(table_name)} #{sql_fragments.join(", ")}" unless sql_fragments.empty? non_combinable_operations.each(&:call) end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 703 def change_column_for_alter(table_name, column_name, type, options = {}) sqls = [change_column_sql(table_name, column_name, type, options)] sqls << change_column_default_for_alter(table_name, column_name, options[:default]) if options.key?(:default) sqls << change_column_null_for_alter(table_name, column_name, options[:null], options[:default]) if options.key?(:null) sqls << Proc.new { change_column_comment(table_name, column_name, options[:comment]) } if options.key?(:comment) sqls end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 686 def change_column_sql(table_name, column_name, type, options = {}) quoted_column_name = quote_column_name(column_name) sql_type = type_to_sql(type, options) sql = "ALTER COLUMN #{quoted_column_name} TYPE #{sql_type}".dup if options[:collation] sql << " COLLATE \"#{options[:collation]}\"" end if options[:using] sql << " USING #{options[:using]}" elsif options[:cast_as] cast_as_type = type_to_sql(options[:cast_as], options) sql << " USING CAST(#{quoted_column_name} AS #{cast_as_type})" end sql end
Returns the current client message level.
Set the client message level.
Returns the current database collation.
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'
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 22 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
Creates a schema for the given schema name.
Returns the current database ctype.
Returns the current database name.
Returns the current schema name.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 752 def data_source_sql(name = nil, type: nil) scope = quoted_scope(name, type: type) scope[:type] ||= "'r','v','m','p','f'" # (r)elation/table, (v)iew, (m)aterialized view, (p)artitioned table, (f)oreign table sql = "SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace".dup sql << " WHERE n.nspname = #{scope[:schema]}" sql << " AND c.relname = #{scope[:name]}" if scope[:name] sql << " AND c.relkind IN (#{scope[:type]})" sql end
Drops the schema for the given schema name.
Returns the current database encoding format.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 666 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
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 503 def foreign_keys(table_name) scope = quoted_scope(table_name) fk_info = exec_query(<<-SQL.strip_heredoc, "SCHEMA") 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, c.convalidated AS valid 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 = #{scope[:name]} AND t3.nspname = #{scope[:schema]} ORDER BY c.conname SQL 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"]) options[:validate] = row["valid"] ForeignKeyDefinition.new(table_name, row["to_table"], options) end end
Verifies existence of an index with a given name.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 67 def index_name_exists?(table_name, index_name) table = quoted_scope(table_name) index = quoted_scope(index_name) query_value(<<-SQL, "SCHEMA").to_i > 0 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[:name]} AND t.relname = #{table[:name]} AND n.nspname = #{index[:schema]} SQL end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 647 def new_column_from_field(table_name, field) column_name, type, default, notnull, oid, fmod, collation, comment = field type_metadata = fetch_type_metadata(column_name, type, oid.to_i, fmod.to_i) default_value = extract_value_from_default(default) default_function = extract_default_function(default_value, default) PostgreSQLColumn.new( column_name, default_value, type_metadata, !notnull, table_name, default_function, collation, comment: comment.presence, max_identifier_length: max_identifier_length ) end
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 763 def quoted_scope(name = nil, type: nil) schema, name = extract_schema_qualified_name(name) type = \ case type when "BASE TABLE" "'r','p'" when "VIEW" "'v','m'" when "FOREIGN TABLE" "'f'" end scope = {} scope[:schema] = schema ? quote(schema) : "ANY (current_schemas(false))" scope[:name] = quote(name) if name scope[:type] = type if type scope end
Renames an index of a table. Raises error if length of new index name is greater than allowed limit.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 497 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 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')
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 402 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 pk idx = "#{table_name}_pkey" new_idx = "#{new_name}_pkey" execute "ALTER INDEX #{quote_table_name(idx)} RENAME TO #{quote_table_name(new_idx)}" if seq && seq.identifier == "#{table_name}_#{pk}_seq" new_seq = "#{new_name}_#{pk}_seq" execute "ALTER TABLE #{seq.quoted} RENAME TO #{quote_table_name(new_seq)}" end end rename_table_indexes(table_name, new_name) end
Returns true if schema exists.
Returns an array of schema names.
Returns the active schema search path.
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.
Validates the given constraint.
Validates the constraint named constraint_name
on
accounts
.
validate_constraint :accounts, :constraint_name
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 602 def validate_constraint(table_name, constraint_name) return unless supports_validate_constraints? at = create_alter_table table_name at.validate_constraint constraint_name execute schema_creation.accept(at) end # Validates the given foreign key. # # Validates the foreign key on +accounts.branch_id+. # # validate_foreign_key :accounts, :branches # # Validates the foreign key on +accounts.owner_id+. # # validate_foreign_key :accounts, column: :owner_id # # Validates the foreign key named +special_fk_name+ on the +accounts+ table. # # validate_foreign_key :accounts, name: :special_fk_name # # The +options+ hash accepts the same keys as SchemaStatements#add_foreign_key. def validate_foreign_key(from_table, options_or_to_table = {}) return unless supports_validate_constraints? fk_name_to_validate = foreign_key_for!(from_table, options_or_to_table).name validate_constraint from_table, fk_name_to_validate end private def schema_creation PostgreSQL::SchemaCreation.new(self) end def create_table_definition(*args) PostgreSQL::TableDefinition.new(*args) end def create_alter_table(name) PostgreSQL::AlterTable.new create_table_definition(name) end def new_column_from_field(table_name, field) column_name, type, default, notnull, oid, fmod, collation, comment = field type_metadata = fetch_type_metadata(column_name, type, oid.to_i, fmod.to_i) default_value = extract_value_from_default(default) default_function = extract_default_function(default_value, default) PostgreSQLColumn.new( column_name, default_value, type_metadata, !notnull, table_name, default_function, collation, comment: comment.presence, max_identifier_length: max_identifier_length ) end 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 def extract_foreign_key_action(specifier) case specifier when "c"; :cascade when "n"; :nullify when "r"; :restrict end end def change_column_sql(table_name, column_name, type, options = {}) quoted_column_name = quote_column_name(column_name) sql_type = type_to_sql(type, options) sql = "ALTER COLUMN #{quoted_column_name} TYPE #{sql_type}".dup if options[:collation] sql << " COLLATE \"#{options[:collation]}\"" end if options[:using] sql << " USING #{options[:using]}" elsif options[:cast_as] cast_as_type = type_to_sql(options[:cast_as], options) sql << " USING CAST(#{quoted_column_name} AS #{cast_as_type})" end sql end def change_column_for_alter(table_name, column_name, type, options = {}) sqls = [change_column_sql(table_name, column_name, type, options)] sqls << change_column_default_for_alter(table_name, column_name, options[:default]) if options.key?(:default) sqls << change_column_null_for_alter(table_name, column_name, options[:null], options[:default]) if options.key?(:null) sqls << Proc.new { change_column_comment(table_name, column_name, options[:comment]) } if options.key?(:comment) sqls end # Changes the default value of a table column. def change_column_default_for_alter(table_name, column_name, default_or_changes) # :nodoc: column = column_for(table_name, column_name) return unless column default = extract_new_default_value(default_or_changes) alter_column_query = "ALTER COLUMN #{quote_column_name(column_name)} %s" if default.nil? # <tt>DEFAULT NULL</tt> results in the same behavior as <tt>DROP DEFAULT</tt>. However, PostgreSQL will # cast the default to the columns type, which leaves us with a default like "default NULL::character varying". alter_column_query % "DROP DEFAULT" else alter_column_query % "SET DEFAULT #{quote_default_expression(default, column)}" end end def change_column_null_for_alter(table_name, column_name, null, default = nil) #:nodoc: "ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL" end def add_timestamps_for_alter(table_name, options = {}) [add_column_for_alter(table_name, :created_at, :datetime, options), add_column_for_alter(table_name, :updated_at, :datetime, options)] end def remove_timestamps_for_alter(table_name, options = {}) [remove_column_for_alter(table_name, :updated_at), remove_column_for_alter(table_name, :created_at)] end def add_index_opclass(quoted_columns, **options) opclasses = options_for_index_columns(options[:opclass]) quoted_columns.each do |name, column| column << " #{opclasses[name]}" if opclasses[name].present? end end def add_options_for_index_columns(quoted_columns, **options) quoted_columns = add_index_opclass(quoted_columns, options) super end def data_source_sql(name = nil, type: nil) scope = quoted_scope(name, type: type) scope[:type] ||= "'r','v','m','p','f'" # (r)elation/table, (v)iew, (m)aterialized view, (p)artitioned table, (f)oreign table sql = "SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace".dup sql << " WHERE n.nspname = #{scope[:schema]}" sql << " AND c.relname = #{scope[:name]}" if scope[:name] sql << " AND c.relkind IN (#{scope[:type]})" sql end def quoted_scope(name = nil, type: nil) schema, name = extract_schema_qualified_name(name) type = \ case type when "BASE TABLE" "'r','p'" when "VIEW" "'v','m'" when "FOREIGN TABLE" "'f'" end scope = {} scope[:schema] = schema ? quote(schema) : "ANY (current_schemas(false))" scope[:name] = quote(name) if name scope[:type] = type if type scope end def extract_schema_qualified_name(string) name = Utils.extract_schema_qualified_name(string.to_s) [name.schema, name.identifier] end end end end
Validates the given foreign key.
Validates the foreign key on accounts.branch_id
.
validate_foreign_key :accounts, :branches
Validates the foreign key on accounts.owner_id
.
validate_foreign_key :accounts, column: :owner_id
Validates the foreign key named special_fk_name
on the
accounts
table.
validate_foreign_key :accounts, name: :special_fk_name
The options
hash accepts the same keys as
SchemaStatements#add_foreign_key.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 626 def validate_foreign_key(from_table, options_or_to_table = {}) return unless supports_validate_constraints? fk_name_to_validate = foreign_key_for!(from_table, options_or_to_table).name validate_constraint from_table, fk_name_to_validate end private def schema_creation PostgreSQL::SchemaCreation.new(self) end def create_table_definition(*args) PostgreSQL::TableDefinition.new(*args) end def create_alter_table(name) PostgreSQL::AlterTable.new create_table_definition(name) end def new_column_from_field(table_name, field) column_name, type, default, notnull, oid, fmod, collation, comment = field type_metadata = fetch_type_metadata(column_name, type, oid.to_i, fmod.to_i) default_value = extract_value_from_default(default) default_function = extract_default_function(default_value, default) PostgreSQLColumn.new( column_name, default_value, type_metadata, !notnull, table_name, default_function, collation, comment: comment.presence, max_identifier_length: max_identifier_length ) end 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 def extract_foreign_key_action(specifier) case specifier when "c"; :cascade when "n"; :nullify when "r"; :restrict end end def change_column_sql(table_name, column_name, type, options = {}) quoted_column_name = quote_column_name(column_name) sql_type = type_to_sql(type, options) sql = "ALTER COLUMN #{quoted_column_name} TYPE #{sql_type}".dup if options[:collation] sql << " COLLATE \"#{options[:collation]}\"" end if options[:using] sql << " USING #{options[:using]}" elsif options[:cast_as] cast_as_type = type_to_sql(options[:cast_as], options) sql << " USING CAST(#{quoted_column_name} AS #{cast_as_type})" end sql end def change_column_for_alter(table_name, column_name, type, options = {}) sqls = [change_column_sql(table_name, column_name, type, options)] sqls << change_column_default_for_alter(table_name, column_name, options[:default]) if options.key?(:default) sqls << change_column_null_for_alter(table_name, column_name, options[:null], options[:default]) if options.key?(:null) sqls << Proc.new { change_column_comment(table_name, column_name, options[:comment]) } if options.key?(:comment) sqls end # Changes the default value of a table column. def change_column_default_for_alter(table_name, column_name, default_or_changes) # :nodoc: column = column_for(table_name, column_name) return unless column default = extract_new_default_value(default_or_changes) alter_column_query = "ALTER COLUMN #{quote_column_name(column_name)} %s" if default.nil? # <tt>DEFAULT NULL</tt> results in the same behavior as <tt>DROP DEFAULT</tt>. However, PostgreSQL will # cast the default to the columns type, which leaves us with a default like "default NULL::character varying". alter_column_query % "DROP DEFAULT" else alter_column_query % "SET DEFAULT #{quote_default_expression(default, column)}" end end def change_column_null_for_alter(table_name, column_name, null, default = nil) #:nodoc: "ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL" end def add_timestamps_for_alter(table_name, options = {}) [add_column_for_alter(table_name, :created_at, :datetime, options), add_column_for_alter(table_name, :updated_at, :datetime, options)] end def remove_timestamps_for_alter(table_name, options = {}) [remove_column_for_alter(table_name, :updated_at), remove_column_for_alter(table_name, :created_at)] end def add_index_opclass(quoted_columns, **options) opclasses = options_for_index_columns(options[:opclass]) quoted_columns.each do |name, column| column << " #{opclasses[name]}" if opclasses[name].present? end end def add_options_for_index_columns(quoted_columns, **options) quoted_columns = add_index_opclass(quoted_columns, options) super end def data_source_sql(name = nil, type: nil) scope = quoted_scope(name, type: type) scope[:type] ||= "'r','v','m','p','f'" # (r)elation/table, (v)iew, (m)aterialized view, (p)artitioned table, (f)oreign table sql = "SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace".dup sql << " WHERE n.nspname = #{scope[:schema]}" sql << " AND c.relname = #{scope[:name]}" if scope[:name] sql << " AND c.relkind IN (#{scope[:type]})" sql end def quoted_scope(name = nil, type: nil) schema, name = extract_schema_qualified_name(name) type = \ case type when "BASE TABLE" "'r','p'" when "VIEW" "'v','m'" when "FOREIGN TABLE" "'f'" end scope = {} scope[:schema] = schema ? quote(schema) : "ANY (current_schemas(false))" scope[:name] = quote(name) if name scope[:type] = type if type scope end def extract_schema_qualified_name(string) name = Utils.extract_schema_qualified_name(string.to_s) [name.schema, name.identifier] end end end