class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter

Constants

ON_COMMIT_VALUES
TABLESPACE_PARAMETERS

Public Instance Methods

add_check_constraint(table, expression, options = {}) click to toggle source

Adds a CHECK constraint to the table. See PostgreSQLCheckConstraint for usage.

# File lib/active_record/postgresql_extensions/constraints.rb, line 33
def add_check_constraint(table, expression, options = {})
  add_constraint(table, PostgreSQLCheckConstraint.new(self, expression, options))
end
add_constraint(table, constraint) click to toggle source

Adds a generic constraint.

# File lib/active_record/postgresql_extensions/constraints.rb, line 27
def add_constraint(table, constraint)
  execute("ALTER TABLE #{quote_table_name(table)} ADD #{constraint};")
end
add_enum_value(enum, value, options = {}) click to toggle source

Adds a new value to an ENUM.

Options

  • :before - add the new value before this value.

  • :after - add the new value after this value.

  • :if_not_exists - adds the value if it doesn’t already exist. Available in PostgreSQL 9.3+.

# File lib/active_record/postgresql_extensions/types.rb, line 49
def add_enum_value(enum, value, options = {})
  assert_valid_add_enum_value_options(options)

  sql = "ALTER TYPE #{quote_generic(enum)} ADD VALUE"

  if options.key?(:if_not_exists)
    ActiveRecord::PostgreSQLExtensions::Features.check_feature(:type_if_not_exists)

    sql << " IF NOT EXISTS" if options[:if_not_exists]
  end

  sql << " #{quote(value)}"

  if options[:before]
    sql << " BEFORE #{quote(options[:before])}"
  elsif options[:after]
    sql << " AFTER #{quote(options[:after])}"
  end

  execute("#{sql};")
end
add_exclude_constraint(table, excludes, options = {}) click to toggle source

Adds an EXCLUDE constraint to the table. See PostgreSQLExcludeConstraint for details.

# File lib/active_record/postgresql_extensions/constraints.rb, line 52
def add_exclude_constraint(table, excludes, options = {})
  add_constraint(table, PostgreSQLExcludeConstraint.new(self, table, excludes, options))
end
add_foreign_key(table, columns, ref_table, *args)
add_foreign_key_constraint(table, columns, ref_table, *args) click to toggle source

Adds a FOREIGN KEY constraint to the table. See PostgreSQLForeignKeyConstraint for details.

# File lib/active_record/postgresql_extensions/constraints.rb, line 45
def add_foreign_key_constraint(table, columns, ref_table, *args)
  add_constraint(table, PostgreSQLForeignKeyConstraint.new(self, columns, ref_table, *args))
end
Also aliased as: add_foreign_key
add_or_alter_text_search_configuration_mapping(name, tokens, dictionaries, options = {}) click to toggle source

This method is semi-private and should only really be used via add_text_search_configuration_mapping and alter_text_search_configuration_mapping.

Options

  • :action - either :add or :alter.

# File lib/active_record/postgresql_extensions/text_search.rb, line 45
def add_or_alter_text_search_configuration_mapping(name, tokens, dictionaries, options = {})
  options = {
    :action => :add
  }.merge(options)

  if ![ :add, :alter ].include?(options[:action])
    raise ArgumentError.new(":action option must be eithe :add or :alter.")
  end

  add_or_alter = options[:action].to_s.upcase

  sql = "ALTER TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} #{add_or_alter} MAPPING FOR "
  sql << Array.wrap(tokens).collect { |token|
    quote_generic(token)
  }.join(', ')

  sql << ' WITH '

  sql << Array.wrap(dictionaries).collect { |dictionary|
    quote_generic(dictionary)
  }.join(', ')

  execute("#{sql};")
end
add_primary_key(table, columns, options = {})
add_primary_key_constraint(table, columns, options = {}) click to toggle source

Adds a PRIMARY KEY constraint to the table. See PostgreSQLPrimaryKeyConstraint for details.

# File lib/active_record/postgresql_extensions/constraints.rb, line 58
def add_primary_key_constraint(table, columns, options = {})
  add_constraint(table, PostgreSQLPrimaryKeyConstraint.new(self, columns, options))
end
Also aliased as: add_primary_key
add_text_search_configuration_mapping(name, tokens, dictionaries) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 31
def add_text_search_configuration_mapping(name, tokens, dictionaries)
  add_or_alter_text_search_configuration_mapping(name, tokens, dictionaries, :action => :add)
end
add_unique_constraint(table, columns, options = {}) click to toggle source

Adds a UNIQUE constraint to the table. See PostgreSQLUniqueConstraint for details.

# File lib/active_record/postgresql_extensions/constraints.rb, line 39
def add_unique_constraint(table, columns, options = {})
  add_constraint(table, PostgreSQLUniqueConstraint.new(self, columns, options))
end
alter_event_trigger_owner(name, role) click to toggle source

Reassigns ownership of an event trigger.

# File lib/active_record/postgresql_extensions/event_triggers.rb, line 49
def alter_event_trigger_owner(name, role)
  execute "ALTER EVENT TRIGGER #{quote_generic(name)} OWNER TO #{quote_generic(role)};"
end
alter_extension(name, options = {}) { |alterer| ... } click to toggle source

Alters an extension. Can be used with an options Hash or in a bloack. For instance, all of the following examples should produce the same output.

# with options Hash
alter_extension(:foo, :collation => 'en_CA.UTF-8')
alter_extension(:foo, :add_collation => 'en_CA.UTF-8')

# block mode
alter_extension(:foo) do |e|
  e.collation 'en_CA.UTF-8'
end

alter_extension(:foo) do |e|
  e.add_collation 'en_CA.UTF-8'
end

# All produce
#
# ALTER EXTENSION "foo" ADD COLLATION "en_CA.UTF-8";

Three versions of each option are available:

  • add_OPTION;

  • drop_OPTION; and

  • OPTION, which is equiavlent to add_OPTION.

See the PostgreSQL docs for a list of all of the available extension options.

Per-Option, uh… Options

:cast, :operator, :operator_class and :operator_family can be set their options as a Hash like so:

# With the options Hash being the actual values:
alter_extension(:foo, :cast => { :hello => :world })

# With the options Hash containing key-values:
alter_extension(:foo, :cast => {
  :source => :hello,
  :target => :world
})

# Or with an Array thusly:
alter_extension(:foo, :cast => [ :source_type, :target_type ])

# Or with arguments like this here:
alter_extension(:foo) do |e|
  e.cast :source_type, :target_type
end

The options themselves even have options! It’s options all the way down!

  • :aggregate - :name and :types.

  • :cast - :source and :target.

  • :function - :name and :arguments. The :arguments option is just a straight up String like in the other function manipulation methods.

  • :operator - :name, :left_type and :right_type.

  • :operator_class and :operator_family - :name and :indexing_method.

# File lib/active_record/postgresql_extensions/extensions.rb, line 122
def alter_extension(name, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

  alterer = PostgreSQLExtensionAlterer.new(self, name, options)

  if block_given?
    yield alterer
  end

  execute(alterer.to_s) unless alterer.empty?
end
alter_extension_schema(name, schema) click to toggle source
# File lib/active_record/postgresql_extensions/extensions.rb, line 48
def alter_extension_schema(name, schema)
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

  execute "ALTER EXTENSION #{quote_generic(name)} SET SCHEMA #{quote_schema(schema)};"
end
alter_function(name, *args) { |alterer| ... } click to toggle source

Alters a function. There’s a ton of stuff you can do here, and there’s two ways to do it: with a block or with an options Hash.

In both cases, you’re going to be using the same options as defined in create_function with the exception of :force and :delimiter and with the addition of :reset. The :reset option allows you to reset the values of parameters used with :set either on an individual basis using an Array or by using :all to reset all of them.

Examples

Both of the following examples should produce the same output.

# with options Hash
alter_function('my_function', 'integer', :rename_to => 'another_function')
alter_function('another_function', 'integer', :owner_to => 'jdoe')

# block mode
alter_function('my_function', 'integer') do |f|
  f.rename_to 'another_function'
  f.owner_to 'jdoe'
end

# Produces:
#
# ALTER FUNCTION "my_function"(integer) OWNER TO "jdoe";
# ALTER FUNCTION "my_function"(integer) RENAME TO "another_function";
# File lib/active_record/postgresql_extensions/functions.rb, line 236
def alter_function(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(0, 2)

  options = args.extract_options!
  arguments = args.pop
  alterer = PostgreSQLFunctionAlterer.new(self, name, arguments, options)

  if block_given?
    yield alterer
  end

  execute alterer.to_s unless alterer.empty?
end
alter_function_owner(name, *args) click to toggle source

Changes the function’s owner.

# File lib/active_record/postgresql_extensions/functions.rb, line 186
def alter_function_owner(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(1, 2)

  options = args.extract_options!
  owner_to = args.pop
  arguments = args.pop

  execute PostgreSQLFunctionAlterer.new(self, name, arguments, :owner_to => owner_to).to_s
end
alter_function_schema(name, *args) click to toggle source

Changes the function’s schema.

# File lib/active_record/postgresql_extensions/functions.rb, line 197
def alter_function_schema(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(1, 2)

  options = args.extract_options!
  set_schema = args.pop
  arguments = args.pop

  execute PostgreSQLFunctionAlterer.new(self, name, arguments, :set_schema => set_schema).to_s
end
alter_index_tablespace(name, tablespace, options = {}) click to toggle source

Changes an index’s tablespace.

# File lib/active_record/postgresql_extensions/indexes.rb, line 161
def alter_index_tablespace(name, tablespace, options = {})
  execute "ALTER INDEX #{quote_generic(name)} SET TABLESPACE #{quote_tablespace(tablespace)};"
end
alter_language_name(old_language, new_language, options = {}) click to toggle source

Renames a language.

# File lib/active_record/postgresql_extensions/languages.rb, line 58
def alter_language_name(old_language, new_language, options = {})
  execute "ALTER PROCEDURAL LANGUAGE #{quote_language(old_language)} RENAME TO #{quote_language(new_language)};"
end
alter_language_owner(language, role, options = {}) click to toggle source

Changes a language’s owner.

# File lib/active_record/postgresql_extensions/languages.rb, line 63
def alter_language_owner(language, role, options = {})
  execute "ALTER PROCEDURAL LANGUAGE #{quote_language(language)} OWNER TO #{quote_role(role)};"
end
alter_materialized_view_drop_column_default(name, column, options = {}) click to toggle source

Drop the default value on a materialized view column

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 87
def alter_materialized_view_drop_column_default(name, column, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :drop_default => column
  }, options).to_sql
end
alter_materialized_view_owner(name, role, options = {}) click to toggle source

Change the ownership of a materialized view.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 94
def alter_materialized_view_owner(name, role, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :owner_to => role
  }, options).to_sql
end
alter_materialized_view_reset_options(name, *args) click to toggle source

Resets a materialized view’s options.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 115
def alter_materialized_view_reset_options(name, *args)
  options = args.extract_options!

  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :reset_options => args
  }, options).to_sql
end
alter_materialized_view_schema(name, schema, options = {}) click to toggle source

Alter a materialized view’s schema.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 101
def alter_materialized_view_schema(name, schema, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :set_schema => schema
  }, options).to_sql
end
alter_materialized_view_set_column_default(name, column, default, options = {}) click to toggle source

Change the default of a materialized view column. The default value can be either a straight-up value or a Hash containing an expression in the form :expression => value which will be passed through unescaped. This allows you to set expressions and use functions and the like.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 79
def alter_materialized_view_set_column_default(name, column, default, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :column => column,
    :set_default => default
  }, options).to_sql
end
alter_materialized_view_set_options(name, set_options, options = {}) click to toggle source

Sets a materialized view’s options using a Hash.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 108
def alter_materialized_view_set_options(name, set_options, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :set_options => set_options
  }, options).to_sql
end
alter_role(name, options = {}) click to toggle source

Alters a PostgreSQL ROLE. See PostgreSQLRole for details on options.

# File lib/active_record/postgresql_extensions/roles.rb, line 20
def alter_role(name, options = {})
  execute PostgreSQLRole.new(self, :alter, name, options).to_sql
end
Also aliased as: alter_user
alter_schema_name(old_schema, new_schema) click to toggle source

Alter’s a schema’s name.

# File lib/active_record/postgresql_extensions/schemas.rb, line 52
def alter_schema_name(old_schema, new_schema)
  execute("ALTER SCHEMA #{quote_schema(old_schema)} RENAME TO #{quote_schema(new_schema)};")
end
alter_schema_owner(schema, role) click to toggle source

Changes a schema’s owner.

# File lib/active_record/postgresql_extensions/schemas.rb, line 57
def alter_schema_owner(schema, role)
  execute("ALTER SCHEMA #{quote_schema(schema)} OWNER TO #{quote_role(role)};")
end
alter_sequence(name, options = {}) click to toggle source

Alters any of the various options for a sequence. See create_sequence for details on the available options. In addition to the options provided by create_sequence, there is also the :restart_with option, which resets the sequence to a new starting value and sets the is_called flag to false, which would be the equivalent of calling the PostgreSQL function setval with a false value in the third parameter.

# File lib/active_record/postgresql_extensions/sequences.rb, line 95
def alter_sequence(name, options = {})
  execute(PostgreSQLSequenceDefinition.new(self, :alter, name, options).to_s)
end
alter_sequence_schema(name, schema, options = {}) click to toggle source

Alters the sequence’s schema.

# File lib/active_record/postgresql_extensions/sequences.rb, line 83
def alter_sequence_schema(name, schema, options = {})
  execute("ALTER SEQUENCE #{quote_sequence(name)} SET SCHEMA #{quote_schema(schema)};")
end
alter_table_schema(table_name, schema, options = {}) click to toggle source

Set the schema of a table.

# File lib/active_record/postgresql_extensions/tables.rb, line 17
def alter_table_schema(table_name, schema, options = {})
  execute "ALTER TABLE #{quote_schema(table_name)} SET SCHEMA #{quote_schema(schema)};"
end
alter_tablespace_owner(tablespace, role) click to toggle source

Changes a tablespace’s owner.

# File lib/active_record/postgresql_extensions/tablespaces.rb, line 44
def alter_tablespace_owner(tablespace, role)
  execute("ALTER TABLESPACE #{quote_tablespace(tablespace)} OWNER TO #{quote_role(role)};")
end
alter_tablespace_parameters(tablespace, parameters_and_values) click to toggle source
# File lib/active_record/postgresql_extensions/tablespaces.rb, line 48
def alter_tablespace_parameters(tablespace, parameters_and_values)
  sql = "ALTER TABLESPACE #{quote_tablespace(tablespace)} SET ("

  sql << parameters_and_values.collect { |k, v|
    assert_valid_tablespace_parameter(k)
    "\n  #{quote_generic(k)} = #{v}"
  }.join(",")

  sql << "\n);"

  execute(sql)
end
alter_text_search_configuration_mapping(name, tokens, dictionaries) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 35
def alter_text_search_configuration_mapping(name, tokens, dictionaries)
  add_or_alter_text_search_configuration_mapping(name, tokens, dictionaries, :action => :alter)
end
alter_text_search_configuration_mapping_replace_dictionary(name, mappings, old_dictionary, new_dictionary) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 77
def alter_text_search_configuration_mapping_replace_dictionary(name, mappings, old_dictionary, new_dictionary)
  if mappings.blank?
    raise ArgumentError.new("Expected one or more mappings to alter.")
  end

  sql = "ALTER TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} ALTER MAPPING FOR "
  sql << Array.wrap(mappings).collect { |token_type|
    quote_generic(token_type)
  }.join(', ')
  sql << " REPLACE #{quote_generic(old_dictionary)} WITH #{quote_generic(new_dictionary)}"

  execute("#{sql};")
end
alter_text_search_configuration_owner(name, role) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 120
def alter_text_search_configuration_owner(name, role)
  execute "ALTER TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} OWNER TO #{quote_role(role)};"
end
alter_text_search_configuration_schema(name, schema) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 124
def alter_text_search_configuration_schema(name, schema)
  execute "ALTER TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} SET SCHEMA #{quote_schema(schema)};"
end
alter_text_search_dictionary(name, options) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 172
def alter_text_search_dictionary(name, options)
  if options.blank?
    raise ArgumentError.new("Expected some options to alter.")
  end

  sql = "ALTER TEXT SEARCH DICTIONARY #{quote_generic_with_schema} ("
  sql << options.collect { |k, v|
    "#{quote_generic(k)} = #{quote(v)}"
  }.join(', ')
  sql << ')'

  execute("#{sql};")
end
alter_text_search_dictionary_owner(name, role) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 193
def alter_text_search_dictionary_owner(name, role)
  execute "ALTER TEXT SEARCH DICTIONARY #{quote_generic_with_schema(name)} OWNER TO #{quote_role(role)};"
end
alter_text_search_dictionary_schema(name, schema) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 197
def alter_text_search_dictionary_schema(name, schema)
  execute "ALTER TEXT SEARCH DICTIONARY #{quote_generic_with_schema(name)} SET SCHEMA #{quote_schema(schema)};"
end
alter_text_search_parser_schema(name, schema) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 291
def alter_text_search_parser_schema(name, schema)
  execute "ALTER TEXT SEARCH PARSER #{quote_generic_with_schema(name)} SET SCHEMA #{quote_schema(schema)};"
end
alter_text_search_template_schema(name, schema) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 243
def alter_text_search_template_schema(name, schema)
  execute "ALTER TEXT SEARCH TEMPLATE #{quote_generic_with_schema(name)} SET SCHEMA #{quote_schema(schema)};"
end
alter_user(name, options = {})
Alias for: alter_role
alter_view_drop_column_default(name, column, options = {}) click to toggle source

Drop a column default from a view.

# File lib/active_record/postgresql_extensions/views.rb, line 115
def alter_view_drop_column_default(name, column, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :drop_default => column
  }, options).to_sql
end
alter_view_owner(name, role, options = {}) click to toggle source

Change the ownership of a view.

# File lib/active_record/postgresql_extensions/views.rb, line 76
def alter_view_owner(name, role, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :owner_to => role
  }, options).to_sql
end
alter_view_reset_options(name, *args) click to toggle source

Resets a view’s options.

# File lib/active_record/postgresql_extensions/views.rb, line 97
def alter_view_reset_options(name, *args)
  options = args.extract_options!

  execute PostgreSQLViewAlterer.new(self, name, {
    :reset_options => args
  }, options).to_sql
end
alter_view_schema(name, schema, options = {}) click to toggle source

Alter a view’s schema.

# File lib/active_record/postgresql_extensions/views.rb, line 83
def alter_view_schema(name, schema, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :set_schema => schema
  }, options).to_sql
end
alter_view_set_column_default(name, column, expression, options = {}) click to toggle source

Set a column default on a view.

# File lib/active_record/postgresql_extensions/views.rb, line 106
def alter_view_set_column_default(name, column, expression, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :set_default => {
      column => expression
    }
  }, options).to_sql
end
alter_view_set_options(name, set_options, options = {}) click to toggle source

Sets a view’s options using a Hash.

# File lib/active_record/postgresql_extensions/views.rb, line 90
def alter_view_set_options(name, set_options, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :set_options => set_options
  }, options).to_sql
end
change_table(table_name, options = {}) { |table| ... } click to toggle source
# File lib/active_record/postgresql_extensions/tables.rb, line 124
def change_table(table_name, options = {})
  table = PostgreSQLTable.new(table_name, self)
  yield table

  unless table.post_processing.blank?
    table.post_processing.each do |pp|
      execute pp.to_s
    end
  end
end
Also aliased as: original_change_table
cluster(name, options = {}) click to toggle source

Cluster a table or materialized view on an index.

Options

  • :using - adds a USING clause to cluster on. If no :using option is provided, the object itself will be re-clustered.

  • :verbose - Adds the VERBOSE clause.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 698
def cluster(name, options = {})
  sql = 'CLUSTER '
  sql << 'VERBOSE ' if options[:verbose]
  sql << quote_table_name(name)
  sql << " USING #{quote_generic(options[:using])}" if options[:using]

  execute "#{sql};"
end
cluster_all(options = {}) click to toggle source

Run the CLUSTER command on all previously clustered tables available to be clustered by the current user.

Options

  • :verbose - Adds the VERBOSE clause.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 683
def cluster_all(options = {})
  sql = 'CLUSTER'
  sql << ' VERBOSE' if options[:verbose]

  execute "#{sql};"
end
cluster_materialized_view(name, index_name) click to toggle source

Cluster a materialized view on an index.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 124
def cluster_materialized_view(name, index_name)
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :cluster_on => index_name
  }).to_sql
end
copy_from(table_name, file, options = {}) click to toggle source

Copies the contents of a file into a table. This uses PostgreSQL’s COPY FROM command.

The COPY FROM command requires the input file to be readable on the server the database is actually running on. In our method, you have the choice of a file on your client’s local file system or one on the server’s local file system. See the :local option below.

See the PostgreSQL documentation for details on COPY FROM.

Options

  • :columns - allows you to specify column names.

  • :binary - adds the BINARY clause.

  • :oids - adds the OIDS clause.

  • :delimiter - sets the delimiter for the data fields. The default COPY FROM delimiter in ASCII mode is a tab character, while in CSV it is a comma.

  • :null - allows you to set a default value for null fields. The default for this option is unset.

  • :local - allows you to specify that the file to be copied from is on a file system that is directly accessible from the database server itself. The default is true, i.e. the file is local to the client. See below for a more thorough explanation.

  • :csv - allows you to specify a CSV file. This option can be set to true, in which case you’ll be using the server defaults for its CSV imports, or a Hash, in which case you can modify various CSV options like quote and escape characters.

CSV Options
  • :header - uses the first line as a CSV header row and skips over it.

  • :quote - the character to use for quoting. The default is a double-quote.

  • :escape - the character to use when escaping a quote character. Usually this is another double-quote.

  • :not_null - allows you to specify one or more columns to be inserted with a default value rather than NULL for any missing values.

  • :freeze - a performance enhancement added in PostgreSQL 9.3. See the PostgreSQL documentation for details.

  • :encoding - set the encoding of the input. Available in PostgreSQL 9.1+.

Local Server Files vs. Local Client Files vs. PROGRAM

The copy_from method allows you to import rows from a file that exists on either your client’s file system or on the database server’s file system using the :local option.

PostgreSQL 9.3 additionally introduced the PROGRAM option to COPY FROM that allows you to pipe the output of a shell command to STDIN. This option requires that the COPY FROM command be run from on the server and as such may be limited by server restrictions such as access controls and permissions.

To process a file on the remote database server’s file system:

  • the file must be given as an absolute path or as a valid shell command if using the PROGRAM option;

  • must be readable by the user that the actual PostgreSQL database server runs under; and

  • the COPY FROM command itself can only be performed by database superusers.

In comparison, reading the file from the local client does not have restrictions enforced by PostgreSQL and can be performed on the client machine. When using a local file, the file itself is actually opened in Ruby and pushed into the database via a “COPY FROM STDIN” command. Thus, the file must be readable by the user your Ruby process is running as. PostgreSQL will not enforce the superuser restriction in this case since you are not touching the database server’s local file system.

Some considerations:

  • A copy from the database’s local file system is faster than a local copy, as the data need not be read into Ruby and dumped across the network or UNIX socket to the database server.

  • A local copy is generally more flexible as it bypasses some of PostgreSQL’s security considerations.

  • Copies from the server’s file system require that the file exists on the file system accessible to the database server, something that you may not even have access to in the first place.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 318
def copy_from(table_name, file, options = {})
  options = {
    :local => true
  }.merge(options)

  assert_valid_copy_from_options(options)

  sql = "COPY #{quote_table_name(table_name)}"

  unless options[:columns].blank?
    sql << ' (' << Array.wrap(options[:columns]).collect { |c| quote_column_name(c) }.join(', ') << ')'
  end

  if options[:program]
    sql << " FROM PROGRAM #{quote(file)}"
  elsif options[:local]
    sql << " FROM STDIN"
  else
    sql << " FROM #{quote(file)}"
  end

  sql << ' FREEZE' if options[:freeze]
  sql << ' BINARY' if options[:binary]
  sql << ' OIDS' if options[:oids]
  sql << " DELIMITER AS #{quote(options[:delimiter])}" if options[:delimiter]
  sql << " NULL AS #{quote(options[:null_as])}" if options[:null]
  sql << " ENCODING #{quote(options[:encoding])}" if options[:encoding]

  if options[:csv]
    sql << ' CSV'
    if options[:csv].is_a?(Hash)
      sql << ' HEADER' if options[:csv][:header]
      sql << " QUOTE AS #{quote(options[:csv][:quote])}" if options[:csv][:quote]
      sql << " ESCAPE AS #{quote(options[:csv][:escape])}" if options[:csv][:escape]
      sql << ' FORCE NOT NULL ' << Array.wrap(options[:csv][:not_null]).collect do |c|
        quote_column_name(c)
      end.join(', ') if options[:csv][:not_null]
    end
  end

  sql << ';'

  if options[:program] || !options[:local]
    execute sql
  else
    fp = File.open(file, 'r')

    if self.raw_connection.respond_to?(:copy_data)
      self.raw_connection.copy_data(sql) do
        fp.each do |l|
          self.raw_connection.put_copy_data(l)
        end
      end
    else
      execute sql
      fp.each do |l|
        self.raw_connection.put_copy_data(l)
      end
      self.raw_connection.put_copy_end
    end
  end
end
Also aliased as: copy_from_file
copy_from_file(table_name, file, options = {})
Alias for: copy_from
create_enum(name, *values) click to toggle source

Creates an ENUM TYPE. An ENUM can contain zero or more values. ENUMs can be dropped with drop_type.

# File lib/active_record/postgresql_extensions/types.rb, line 37
def create_enum(name, *values)
  execute PostgreSQLEnumDefinition.new(self, name, *values).to_s
end
create_event_trigger(name, event, function, options = {}) click to toggle source

Creates a PostgreSQL event trigger. Available in PostgreSQL 9.3+.

event is one of the valid event trigger event names. See the PostgreSQL documentation for details.

Options

Example

# File lib/active_record/postgresql_extensions/event_triggers.rb, line 22
def create_event_trigger(name, event, function, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:event_triggers)

  execute PostgreSQLEventTriggerDefinition.new(self, name, event, function, options).to_s
end
create_extension(name, options = {}) click to toggle source

Creates a new PostgreSQL text search configuration. You must provide either a parser_name or a source_config option as per the PostgreSQL text search docs.

# File lib/active_record/postgresql_extensions/extensions.rb, line 10
def create_extension(name, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

  sql = "CREATE EXTENSION "
  sql << "IF NOT EXISTS " if options[:if_not_exists]
  sql << quote_generic(name)
  sql << " SCHEMA #{quote_generic(options[:schema])}" if options[:schema]
  sql << " VERSION #{quote_generic(options[:version])}" if options[:version]
  sql << " FROM #{quote_generic(options[:old_version])}" if options[:old_version]

  execute("#{sql};")
end
create_function(name, arguments, returns, language, options = {}, &block) click to toggle source
create_function(name, arguments, returns, language, body, options = {})
create_function(name, returns, language, options = {}, &block)

Creates a PostgreSQL function/stored procedure.

arguments is a simple String that you can use to represent the function arguments.

returns is the return type for the function.

language is the procedural language the function is written in. The possible values for this argument will depend on your database set up. See create_language for details on adding new languages to your database.

body is the actual function body. When the function language is C, this will be an Array containing two items: the object file the function is found in and the link symbol for the function. In all other cases, this argument will be a String containing the actual function code. You can also provide either a block that returns the function body as a String or a :body option.

Options

  • :force - add an OR REPLACE clause to the statement, thus overwriting any existing function definition of the same name and arguments.

  • :behavior - one of :immutable, :stable or :volatile. This option helps the server when making planning estimates when the function is called. The default is :volatile.

  • :on_null_inputs - one of :called, :returns or :strict. This indicates to the server how the function should be called when it receives NULL inputs. When :called is used, the function is executed even when one or more of its arguments is NULL. For :returns and :strict (which are actually just aliases), function execution is skipped and NULL is returned immediately.

  • :security - one of :invoker or :definer. This option determines what privileges the function should used when called. The values are pretty self explanatory. The default is :invoker.

  • :delimiter - the delimiter to use for the function body. The default is ‘$$’.

  • :cost - a number that determines the approximate overhead the server can expect when calling this function. This is used when calculating execution costs in the planner.

  • :rows - a number indicating the estimated number of rows the function will return. This is used when calculating execution costs in the planner and only affects functions that return result sets.

  • :set - allows you to set parameters temporarily during function execution. This would include things like search_path or time zone and such. This option can either be a String with the set fragment or a Hash with the parameters as keys and the values to set as values. When using a Hash, the value :from_current can be used to specify the actual FROM CURRENT clause.

  • :body - allows you to set a function body when the arguments to create_function are ambiguous.

You should definitely check out the PostgreSQL documentation on creating stored procedures, because it can get pretty convoluted as evidenced by the plethora of options we’re handling here.

Examples

# With a string for the body:
create_function('tester_function', 'integer',
  'integer', 'sql',
  'select $1;'
  :behavior => :immutable, :set => { :search_path => :from_current }, :force => true
)

# With a block:
create_function('tester_function', 'integer',
  'integer', 'sql', :behavior => :immutable, :set => { :search_path => :from_current }, :force => true) do
  "select $1;"
end

# Both produce:
#
# CREATE OR REPLACE FUNCTION "tester_function"(integer) RETURNS integer AS $$
#   select $1;
# $$
# LANGUAGE "sql"
#   IMMUTABLE
#   SET "search_path" FROM CURRENT;
# File lib/active_record/postgresql_extensions/functions.rb, line 123
def create_function(name, *args)
  options = args.extract_options!

  raise ArgumentError.new("Expected 3-6 arguments") unless args.length.between?(2, 4)
  raise ArgumentError.new("Ambiguous arguments: can't specify a function body as an argument without any function arguments. Hint: Use a :body option.") if args.length <= 3 && !block_given? && !options.key?(:body)

  arguments, returns, language = if args.length >= 3
    args.shift(3)
  else
    [ nil ] + args.shift(2)
  end

  body = if options.key?(:body)
    if block_given? || args.first.present?
      raise ArgumentError.new("Can't have both a :body option as well as a block or body argument in create_function")
    end

    options[:body].to_s
  elsif args.first.present?
    if block_given?
      raise ArgumentError.new("Can't have both a function body argument as well as a block in create_function")
    end

    args.first
  elsif block_given?
    yield.to_s
  end

  execute PostgreSQLFunctionDefinition.new(self, name, arguments, returns, language, body, options).to_s
end
create_index(name, object, columns, options = {}) click to toggle source

Creates an index. This method is an alternative to the standard ActiveRecord add_index method and includes PostgreSQL-specific options. Indexes can be created on tables as well as materialized views starting with PostgreSQL 9.3.

Differences to add_index

  • With the standard ActiveRecord add_index method, ActiveRecord will automatically generate an index name. With create_index, you need to supply a name yourself. This is due to the fact that PostgreSQL’s indexes can include things like expressions and special index types, so we’re not going to try and parse your expressions for you. You’ll have to supply your own index name.

  • Several PostgreSQL-specific options are included. See below for details.

  • The columns argument supports Hashes to allow for expressions. See examples below.

Options

  • :unique - adds UNIQUE to the index definition.

  • :concurrently - adds CONCURRENTLY to the index definition. See the PostgreSQL documentation for a discussion on concurrently reindexing tables.

  • :using - the indexing method to use. PostgreSQL supports serveral indexing methods out of the box, the default being a binary tree method. For certain column types, alternative indexing methods produce better indexing results. In some cases, a btree index would be pointless given certain datatypes and queries. For instance, PostGIS’ geometry datatypes should generally be indexed with GiST indexes, while the tsvector full text search datatype should generally be indexed with a GiN index. See the PostgreSQL documentation for details.

  • :fill_factor - sets the FILLFACTOR value for the index. This option tells PostgreSQL how to pack its index pages on disk. As indexes grow, they begin to get spread out over multiple disk pages, thus reducing efficiency. This option allows you to control some of that behaviour. The default value for btree indexes is 90, and any value from 10 to 100 can be used. See the PostgreSQL documentation for more details.

  • :tablespace - sets the tablespace for the index.

  • :conditions - adds an optional WHERE clause to the index. (You can alternatively use the option :where instead.)

  • :index_parameters - a simple String or Hash used to assign index storage parameters. See the PostgreSQL docs for details on the various storage parameters available.

Column Options

You can specify a handful of options on each index column/expression definition by supplying a Hash for the definition rather than a Symbol/String.

  • :column or :expression - you can specify either :column or :expression in the column definition, but not both. When using :column, the column name is quoted properly using PostgreSQL’s quoting rules, while using :expression leaves you on your own.

  • :opclass - an “opclass” (a.k.a. “operator class”) provides hints to the PostgreSQL query planner that allow it to more effectively take advantage of indexes. An opclass effectively tells the planner what operators can be used by an index when searching a column or expression. When creating an index, PostgreSQL generally uses an opclass equivalent to the column datatype (i.e. int4_ops for an integer column). You can override this behaviour when necessary. For instance, in queries involving the LIKE operator on a text column, PostgreSQL will usually only take advantage of an index if the database has been created in the C locale. You can override this behaviour by forcing the index to be created using the text_pattern_ops opclass

  • :order - the order to index the column in. This can be one of :asc or :desc.

  • :nulls - specify whether NULL values should be placed :first or :last in the index.

Examples

# using multiple columns
create_index('this_is_my_index', :foo, [ :id, :ref_id ], :using => :gin)
# => CREATE INDEX "this_is_my_index" ON "foo"("id", "ref_id");

# using expressions
create_index('this_is_another_idx', :foo, { :expression => 'COALESCE(ref_id, 0)' })
# => CREATE INDEX "this_is_another_idx" ON "foo"((COALESCE(ref_id, 0)));

# additional options
create_index('search_idx', :foo, :tsvector, :using => :gin)
# => CREATE INDEX "search_idx" ON "foo" USING "gin"("tsvector");
# File lib/active_record/postgresql_extensions/indexes.rb, line 113
def create_index(name, object, columns, options = {})
  execute PostgreSQLIndexDefinition.new(self, name, object, columns, options).to_s
end
create_language(language, options = {}) click to toggle source

Creates a PostgreSQL procedural language.

Note that you can grant privileges on languages using the grant_language_privileges method and revoke them using revoke_language_privileges.

Options

  • :trusted - adds a TRUSTED clause. Trusted languages in PostgreSQL are given a couple of extra abilities that their untrusted counterparts lust for, such as the ability to touch the server’s local file system. This can be rather important if you need to access external libraries in your language’s functions, such as importing CPAN libraries in plperl. The default is untrusted.

  • :handler - this option is used to point the server in the direction of the procedural language’s hooks and such. It’s generally not required now unless you for some reason need to access a langauge that isn’t currently held in the pg_pltemplate system table.

  • :validator - this option provides a previously declared test function that will be used to test the functionality of the newly-installed procedural language.

You don’t often see people using the :handler and :validator options, and they’re really just kind of here for the sake of completeness.

# File lib/active_record/postgresql_extensions/languages.rb, line 34
def create_language(language, options = {})
  sql = 'CREATE '
  sql << 'TRUSTED ' if options[:trusted]
  sql << "PROCEDURAL LANGUAGE #{quote_language(language)}"
  sql << " HANDLER #{quote_language(options[:call_handler])}" if options[:call_handler]
  sql << " VALIDATOR #{options[:validator]}" if options[:validator]
  execute("#{sql};")
end
create_materialized_view(name, query, options = {}) click to toggle source

Creates a new PostgreSQL materialized view.

name is the name of the view. View quoting works the same as table quoting, so you can use PostgreSQLAdapter#with_schema and friends. See PostgreSQLAdapter#with_schema and PostgreSQLAdapter#quote_table_name for details.

query is the SELECT query to use for the view. This is just a straight-up String, so quoting rules will not apply.

Note that you can grant privileges on views using the grant_view_privileges method and revoke them using revoke_view_privileges.

Options

  • :columns - you can rename the output columns as necessary. Note that this can be an Array and that it must be the same length as the number of output columns created by query.

  • :tablespace - allows you to set the tablespace of a materialized view.

  • :with_data - whether to populate the materialized view upon creation. The default is true.

Examples

create_materialized_view(:foo_view, 'SELECT * FROM bar')
# => CREATE MATERIALIZED VIEW "foo_view" AS SELECT * FROM bar;

create_view(
  { :geospatial => :foo_view },
  'SELECT * FROM bar',
  :columns => [ :id, :name, :the_geom ],
  :with_data => false
)
# => CREATE MATERIALIZED VIEW "geospatial"."foo_view" ("id", "name", "the_geom") AS SELECT * FROM bar WITH NO DATA;
# File lib/active_record/postgresql_extensions/materialized_views.rb, line 44
def create_materialized_view(name, query, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:materialized_views)

  execute PostgreSQLMaterializedViewDefinition.new(self, name, query, options).to_s
end
create_role(name, options = {}) click to toggle source

Creates a PostgreSQL ROLE. See PostgreSQLRole for details on options.

# File lib/active_record/postgresql_extensions/roles.rb, line 14
def create_role(name, options = {})
  execute PostgreSQLRole.new(self, :create, name, options).to_sql
end
Also aliased as: create_user
create_rule(name, event, table, action, commands, options = {}) click to toggle source

Creates a PostgreSQL rule.

event can be one of :select, :insert, :update or :delete.

action can be one of :instead or :also.

commands is the actual query to rewrite to. commands can actually be “NOTHING”, a String representing the commands or an Array of Strings if you have multiple commands you want to fire.

Options

  • :force - add an OR REPLACE clause to the command.

  • :conditions - a WHERE clause to limit the rule. Alternatively, you can also use the :where option.

Examples

create_rule(
  'check_it_out_rule',
  :select,
  :child,
  :instead,
  'select * from public.another', :conditions => 'id = 1'
)
# => CREATE RULE "check_it_out_rule" AS ON SELECT TO "child" WHERE id = 1 DO INSTEAD select * from public.another;
# File lib/active_record/postgresql_extensions/rules.rb, line 48
def create_rule(name, event, table, action, commands, options = {})
  execute PostgreSQLRuleDefinition.new(self, name, event, table, action, commands, options).to_s
end
create_schema(schema, options = {}) click to toggle source

Creates a new PostgreSQL schema.

Note that you can grant privileges on schemas using the grant_schema_privileges method and revoke them using revoke_schema_privileges.

Options

  • :authorization - adds an AUTHORIZATION clause. This is used to set the owner of the schema. This can be changed with alter_schema_owner as necessary.

  • :if_not_exists - adds an IF NOT EXISTS clause. Available in PostgreSQL 9.3+.

# File lib/active_record/postgresql_extensions/schemas.rb, line 20
def create_schema(schema, options = {})
  sql = 'CREATE SCHEMA '

  if options.key?(:if_not_exists)
    ActiveRecord::PostgreSQLExtensions::Features.check_feature(:create_schema_if_not_exists)

    sql << 'IF NOT EXISTS ' if options[:if_not_exists]
  end

  sql << quote_schema(schema)
  sql << " AUTHORIZATION #{quote_role(options[:authorization])}" if options[:authorization]
  execute("#{sql};")
end
create_schema_authorization(name, options = {}) click to toggle source

Creates a schema based on a role name. This is a shortcut to using create_schema with the :authorization option and ensures that the schema created has the same name as the role specified.

Options

  • :if_not_exists - adds IF NOT EXISTS. Available in PostgreSQL 9.3+.

# File lib/active_record/postgresql_extensions/schemas.rb, line 70
def create_schema_authorization(name, options = {})
  sql = 'CREATE SCHEMA'

  if options.key?(:if_not_exists)
    ActiveRecord::PostgreSQLExtensions::Features.check_feature(:create_schema_if_not_exists)

    sql << ' IF NOT EXISTS' if options[:if_not_exists]
  end

  sql << " AUTHORIZATION #{quote_role(name)}"

  execute("#{sql};")
end
create_sequence(name, options = {}) click to toggle source

Creates a sequence.

Note that you can grant privileges on sequences using the grant_sequence_privileges method and revoke them using revoke_sequence_privileges.

Options

  • :temporary - creates a temporary sequence.

  • :incement - sets the sequence increment value.

  • :min_value - sets a minimum value for the sequence. If this value is nil or false, we’ll go with “NO MINVALUE”.

  • :max_value - same as :min_value but for maximum values. Mindblowing.

  • :start - the initial value of the sequence.

  • :cache - the number of future values to cache in the sequence. This is generally dangerous to mess with, so be sure to refer to the PostgreSQL documentation for reasons why.

  • :cycle - whether or not the sequence should cycle.

  • :owned_by - this refers to the table and column that a sequence is owned by. If that column/table were to be dropped in the future, for instance, the sequence would be automatically dropped with it. This option can be set using an Array (as in [ table, column ]) or a Hash (as in { :table => 'foo', :column => 'bar' }).

Example

create_sequence(
  'what_a_sequence_of_events',
  :increment => 2,
  :cache => 2,
  :min_value => nil,
  :max_value => 10,
  :owned_by => [ :foo, :id ]
)
# => CREATE SEQUENCE "what_a_sequence_of_events" INCREMENT BY 2
#    NO MINVALUE MAXVALUE 10 CACHE 2 OWNED BY "foo"."id";
# File lib/active_record/postgresql_extensions/sequences.rb, line 55
def create_sequence(name, options = {})
  execute PostgreSQLSequenceDefinition.new(self, :create, name, options).to_s
end
create_table(table_name, options = {}) { |table_definition| ... } click to toggle source

Creates a new table. We’ve expanded the capabilities of the standard ActiveRecord create_table method to included a host of PostgreSQL-specific functionality.

PostgreSQL-specific Do-dads

PostgreSQL allows for a couple of nifty table creation options that ActiveRecord usually doesn’t account for, so we’re filling in the blanks here.

  • :inherits - PostgreSQL allows you to create tables that inherit the properties of another. PostgreSQL is sometimes referred to as an Object-Relational DBMS rather than a straight-up RDBMS because of stuff like this.

  • :on_commit - allows you to define the behaviour of temporary tables. Allowed values are :preserve_rows (the default, which causes the temporary table to retain its rows at the end of a transaction), :delete_rows (which truncates the table at the end of a transaction) and :drop (which drops the table at the end of a transaction).

  • :tablespace - allows you to set the tablespace of a table.

  • :force - force a table to be dropped before trying to create it. This will pass :if_exists => true to the drop_table method.

  • :cascade_drop - when using the :force, this Jedi mindtrick will pass along the :cascade option to drop_table.

  • :of_type - for “OF type_name” clauses.

  • :if_not_exists - adds the “IF NOT EXISTS” clause.

  • :unlogged - creates an UNLOGGED table.

  • :storage_parameters - a simple String or Hash used to assign table storage parameters. See the PostgreSQL docs for details on the various storage parameters available.

We’re expanding the doors of table definition perception with this exciting new addition to the world of ActiveRecord PostgreSQL adapters.

create_table generally behaves like the standard ActiveRecord create_table method with a couple of notable exceptions:

  • you can add column constraints.

  • you can add constraints to the table itself.

  • you can add LIKE and INHERITS clauses to the definition.

See the PostgreSQL documentation for more detailed on these sorts of things. Odds are that you’ll probably recognize what we’re referring to here if you’re bothering to use this plugin, eh?

Also, do note that you can grant privileges on tables using the grant_table_privileges method and revoke them using revoke_table_privileges.

Examples

create_table(:foo, :inherits => :parent) do |t|
  t.integer :bar_id, :references => :bar
  t.like :base, :including => [ :defaults, :indexes ], :excluding => :constraints
  t.check_constraint "bar_id < 100"
  t.unique_constraint :bar_id
end

# Produces:
#
# CREATE TABLE "foo" (
#   "id" serial primary key,
#   "bar_id" integer DEFAULT NULL NULL,
#   LIKE "base" INCLUDING DEFAULTS INCLUDING INDEXES EXCLUDING CONSTRAINTS,
#   FOREIGN KEY ("bar_id") REFERENCES "bar",
#   CHECK (bar_id < 100),
#   UNIQUE ("bar_id")
# ) INHERITS ("parent");

This is a fairly convoluted example, but there you have it.

Beyond these differences, create_table acts like the original ActiveRecord create_table, which you can actually still access using the original_create_table method if you really, really want to.

Be sure to refer to the PostgreSQL documentation for details on data definition and such.

# File lib/active_record/postgresql_extensions/tables.rb, line 107
def create_table(table_name, options = {})
  if options[:force]
    drop_table(table_name, { :if_exists => true, :cascade => options[:cascade_drop] })
  end

  table_definition = PostgreSQLTableDefinition.new(self, table_name, options)
  yield table_definition if block_given?

  execute table_definition.to_s
  unless table_definition.post_processing.blank?
    table_definition.post_processing.each do |pp|
      execute pp.to_s
    end
  end
end
Also aliased as: original_create_table
create_tablespace(name, location, options = {}) click to toggle source

Creates a new PostgreSQL tablespace.

# File lib/active_record/postgresql_extensions/tablespaces.rb, line 14
def create_tablespace(name, location, options = {})
  sql = "CREATE TABLESPACE #{quote_tablespace(name)} "
  sql << "OWNER #{quote_role(options[:owner])} " if options[:owner]
  sql << "LOCATION #{quote(location)}"

  execute("#{sql};")
end
create_text_search_configuration(name, options = {}) click to toggle source

Creates a new PostgreSQL text search configuration. You must provide either a :parser_name or a :source_config option as per the PostgreSQL text search docs.

# File lib/active_record/postgresql_extensions/text_search.rb, line 10
def create_text_search_configuration(name, options = {})
  if options[:parser_name] && options[:source_config]
    raise ArgumentError.new("You can't define both :parser_name and :source_config options.")
  elsif options[:parser_name].blank? && options[:source_config].blank?
    raise ArgumentError.new("You must provide either a :parser_name or a :source_config.")
  end

  sql = "CREATE TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} ("

  ignore_scoped_schema do
    sql << if options[:parser_name]
      "PARSER = #{quote_generic_with_schema(options[:parser_name])}"
    else
      "COPY = #{quote_generic_with_schema(options[:source_config])}"
    end
  end

  sql << ")"
  execute("#{sql};")
end
create_text_search_dictionary(name, template, options = {}) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 143
def create_text_search_dictionary(name, template, options = {})
  sql = "CREATE TEXT SEARCH DICTIONARY #{quote_generic_with_schema(name)} ("
  sql << "TEMPLATE = #{quote_generic_with_schema(template)}"

  if !options.blank?
    sql << ', '
    sql << options.collect { |k, v|
      "#{quote_generic(k)} = #{quote(v)}"
    }.join(', ')
  end

  sql << ')'

  execute("#{sql};")
end
create_text_search_parser(name, options = {}) click to toggle source

The :start, :gettoken, :end and :lextypes options are required as per the PostgreSQL docs, while the :headline option is optional.

# File lib/active_record/postgresql_extensions/text_search.rb, line 251
def create_text_search_parser(name, options = {})
  if (missing_options = [ :start, :gettoken, :end, :lextypes ] - options.keys).present?
    raise ArgumentError.new("Missing options: #{missing_options}.")
  end

  sql = "CREATE TEXT SEARCH PARSER #{quote_generic_with_schema(name)} ("
  sql << "START = #{quote_function(options[:start])}, "
  sql << "GETTOKEN = #{quote_function(options[:gettoken])}, "
  sql << "END = #{quote_function(options[:end])}, "
  sql << "LEXTYPES = #{quote_function(options[:lextypes])}"

  if options[:headline]
    sql << ", HEADLINE = #{quote_function(options[:headline])}"
  end

  sql << ')'

  execute("#{sql};")
end
create_text_search_template(name, options = {}) click to toggle source

Options

:lexize - the function used by the template lexer. Required. :init - the initialization function for the template. Optional.

# File lib/active_record/postgresql_extensions/text_search.rb, line 206
def create_text_search_template(name, options = {})
  if options[:lexize].blank?
    raise ArgumentError.new("Expected to see a :lexize option.")
  end

  sql = "CREATE TEXT SEARCH TEMPLATE #{quote_generic_with_schema(name)} ("

  if options[:init]
    sql << "INIT = #{quote_function(options[:init])}, "
  end

  sql << "LEXIZE = #{quote_function(options[:lexize])}"
  sql << ')'

  execute("#{sql};")
end
create_trigger(name, called, events, table, function, options = {}) click to toggle source

Creates a PostgreSQL trigger.

The called argument specifies when the trigger is called and can be either :before or :after.

events can be on or more of :insert, :update or :delete. There are no :select triggers, as SELECT generally doesn’t modify the database.

table is obviously the table the trigger will be created on while function is the name of the procedure to call when the trigger is fired.

Options

  • :for_each - defines whether the trigger will be fired on each row in a statement or on the statement itself. Possible values are :row and :statement, with :statement being the default.

  • :args - if the trigger function requires any arguments then this is the place to let everyone know about it.

Example

create_trigger(
  'willie_nelsons_trigger',
  :before,
  :update,
  { :nylon => :guitar },
  'strum_trigger',
  :for_each => :row
)
# => CREATE TRIGGER "willie_nelsons_trigger" BEFORE UPDATE
#    ON "nylon"."guitar" FOR EACH ROW EXECUTE PROCEDURE "test_trigger"();
# File lib/active_record/postgresql_extensions/triggers.rb, line 85
def create_trigger(name, called, events, table, function, options = {})
  execute PostgreSQLTriggerDefinition.new(self, name, called, events, table, function, options).to_s
end
create_user(name, options = {})
Alias for: create_role
create_view(name, query, options = {}) click to toggle source

Creates a new PostgreSQL view.

name is the name of the view. View quoting works the same as table quoting, so you can use PostgreSQLAdapter#with_schema and friends. See PostgreSQLAdapter#with_schema and PostgreSQLAdapter#quote_table_name for details.

query is the SELECT query to use for the view. This is just a straight-up String, so quoting rules will not apply.

Note that you can grant privileges on views using the grant_view_privileges method and revoke them using revoke_view_privileges.

Options

  • :replace - adds a REPLACE clause, as in “CREATE OR REPLACE”.

  • :temporary - adds a TEMPORARY clause.

  • :columns - you can rename the output columns as necessary. Note that this can be an Array and that it must be the same length as the number of output columns created by query.

  • :with_options - sets view options. View options were added in PostgreSQL 9.1. See the PostgreSQL docs for details on the available options.

  • :recursive - adds the RECURSIVE clause. Available in PostgreSQL 9.3+.

Examples

create_view(:foo_view, 'SELECT * FROM bar')
# => CREATE VIEW "foo_view" AS SELECT * FROM bar;

create_view(
  { :geospatial => :foo_view },
  'SELECT * FROM bar',
  :columns => [ :id, :name, :the_geom ]
)
# => CREATE VIEW "geospatial"."foo_view" ("id", "name", "the_geom") AS SELECT * FROM bar;
# File lib/active_record/postgresql_extensions/views.rb, line 47
def create_view(name, query, options = {})
  execute PostgreSQLViewDefinition.new(self, name, query, options).to_s
end
current_role() click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 445
def current_role
  current_role = execute('SELECT current_role;').try(:first)

  if current_role
    current_role['current_user']
  end
end
Also aliased as: current_user
current_scoped_schema() click to toggle source

Get the current scoped schema.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 82
def current_scoped_schema
  scoped_schemas.last
end
current_user()
Alias for: current_role
disable_event_trigger(name) click to toggle source

Disables an event trigger.

# File lib/active_record/postgresql_extensions/event_triggers.rb, line 75
def disable_event_trigger(name)
  execute "ALTER EVENT TRIGGER #{quote_generic(name)} DISABLE;"
end
disable_triggers(table, *triggers) click to toggle source

Disable triggers. If no triggers are specified, all triggers will be disabled. You can specify ALL or USER triggers by using the symbols :all or :user. If you have actual triggers named “all” or “user”, use Strings instead of Symbols.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 526
def disable_triggers(table, *triggers)
  quoted_table_name = quote_table_name(table)
  triggers = if triggers.present?
    triggers.collect { |trigger|
      case trigger
        when :all, :user
          trigger.to_s.upcase
        else
          quote_generic(trigger)
      end
    }
  else
    'ALL'
  end

  Array.wrap(triggers).each do |trigger|
    execute("ALTER TABLE #{quoted_table_name} DISABLE TRIGGER #{trigger};")
  end
end
drop_constraint(table, name, options = {}) click to toggle source

Drops a constraint from the table. Use this to drop CHECK, UNIQUE, EXCLUDE and FOREIGN KEY constraints from a table.

Options:

  • :cascade - set to true to add a CASCADE clause to the command.

# File lib/active_record/postgresql_extensions/constraints.rb, line 70
def drop_constraint(table, name, options = {})
  sql = "ALTER TABLE #{quote_table_name(table)} DROP CONSTRAINT #{quote_generic(name)}"
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
drop_event_trigger(name, options = {}) click to toggle source

Drops an event trigger.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - cascades changes down to objects referring to the trigger.

# File lib/active_record/postgresql_extensions/event_triggers.rb, line 35
def drop_event_trigger(name, options = {})
  sql = 'DROP EVENT TRIGGER '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic(name)
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
drop_extension(*args) click to toggle source

Options

  • if_exists - adds IF EXISTS.

  • cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/extensions.rb, line 27
def drop_extension(*args)
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

  options = args.extract_options!

  sql = 'DROP EXTENSION '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |name| quote_generic(name) }.join(', ')
  sql << ' CASCADE' if options[:cascade]

  execute("#{sql};")
end
drop_function(name, *args) click to toggle source

Drops a function.

Options

  • :if_exists - adds an IF EXISTS clause.

  • :cascade - cascades the operation on to any objects referring to the function.

# File lib/active_record/postgresql_extensions/functions.rb, line 161
def drop_function(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(1, 2)

  options = args.extract_options!
  arguments = args.first

  sql = 'DROP FUNCTION '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << "#{quote_function(name)}(#{arguments})"
  sql << ' CASCADE' if options[:cascade]
  execute "#{sql};"
end
drop_index(*args) click to toggle source

PostgreSQL-specific version of the standard ActiveRecord remove_index method.

Unlike remove_index, you’ll have to specify an actual index name with drop_index. See create_index for the particulars on why.

You can specify multiple INDEXes with an Array when using drop_index, but you may need to use the method directly through the ActiveRecord connection rather than the Migration method, as the Migration method likes to escape the Array to a String.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

  • :concurrently - adds the CONCURRENTLY option when dropping the INDEX. When using the :concurrently option, only one INDEX can specified and the :cascade option cannot be used. See the PostgreSQL documentation for details.

# File lib/active_record/postgresql_extensions/indexes.rb, line 137
def drop_index(*args)
  options = args.extract_options!
  args.flatten!

  if options[:concurrently] && options[:cascade]
    raise ArgumentError.new("The :concurrently and :cascade options cannot be used together.")
  elsif options[:concurrently] && args.length > 1
    raise ArgumentError.new("The :concurrently option can only be used on a single INDEX.")
  end

  sql = 'DROP INDEX '
  sql << 'CONCURRENTLY ' if options[:concurrently]
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |i| quote_generic(i) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
drop_language(language, options = {}) click to toggle source

Drops a language.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/languages.rb, line 49
def drop_language(language, options = {})
  sql = 'DROP PROCEDURAL LANGUAGE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_language(language)
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
drop_materialized_view(*args) click to toggle source

Drops a materialized view.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 56
def drop_materialized_view(*args)
  options = args.extract_options!
  args.flatten!

  sql = 'DROP MATERIALIZED VIEW '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |v| quote_view_name(v) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
drop_role(*args) click to toggle source

Drop PostgreSQL ROLEs.

Options

  • :if_exists - don’t raise an error if the ROLE doesn’t exist. The default is false.

# File lib/active_record/postgresql_extensions/roles.rb, line 31
def drop_role(*args)
  options = args.extract_options!
  args.flatten!

  sql = 'DROP ROLE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |r| quote_role(r) }.join(', ')
  execute("#{sql};")
end
Also aliased as: drop_user
drop_rule(name, table) click to toggle source

Drops a PostgreSQL rule.

# File lib/active_record/postgresql_extensions/rules.rb, line 53
def drop_rule(name, table)
  execute "DROP RULE #{quote_rule(name)} ON #{quote_table_name(table)};"
end
drop_schema(*args) click to toggle source

Drops a schema.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/schemas.rb, line 40
def drop_schema(*args)
  options = args.extract_options!
  args.flatten!

  sql = 'DROP SCHEMA '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |s| quote_schema(s) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
drop_sequence(*args) click to toggle source

Drops a sequence.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - cascades the operation down to objects referring to the sequence.

# File lib/active_record/postgresql_extensions/sequences.rb, line 66
def drop_sequence(*args)
  options = args.extract_options!
  args.flatten!

  sql = 'DROP SEQUENCE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |s| quote_sequence(s) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
drop_table(*args) click to toggle source

Drops a table. This method is expanded beyond the standard ActiveRecord drop_table method to allow for a couple of PostgreSQL-specific options:

  • :if_exists - adds an IF EXISTS clause to the query. In absence of this option, an exception will be raised if you try to drop a table that doesn’t exist.

  • :cascade - adds a CASCADE clause to the query. This will cause references to this table like foreign keys to be dropped as well. See the PostgreSQL documentation for details.

You can still access the original method via original_drop_table.

# File lib/active_record/postgresql_extensions/tables.rb, line 148
def drop_table(*args)
  options = args.extract_options!
  args.flatten!

  sql = 'DROP TABLE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |t| quote_table_name(t) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
Also aliased as: original_drop_table
drop_tablespace(name, options = {}) click to toggle source

Drops a tablespace.

Options

  • :if_exists - adds IF EXISTS.

# File lib/active_record/postgresql_extensions/tablespaces.rb, line 27
def drop_tablespace(name, options = {})
  sql = 'DROP TABLESPACE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_tablespace(name)

  execute("#{sql};")
end
drop_text_search_configuration(name, options = {}) click to toggle source

Drops a text search configuration.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/text_search.rb, line 134
def drop_text_search_configuration(name, options = {})
  sql = 'DROP TEXT SEARCH CONFIGURATION '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic_with_schema(name)
  sql << ' CASCADE' if options[:cascade]

  execute("#{sql};")
end
drop_text_search_configuration_mapping(name, *args) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 91
def drop_text_search_configuration_mapping(name, *args)
  options = args.extract_options!
  mappings = args

  if mappings.blank?
    raise ArgumentError.new("Expected one or more mappings to drop.")
  end

  sql = "ALTER TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} DROP MAPPING "

  if options[:if_exists]
    sql << 'IF EXISTS '
  end

  sql << 'FOR '
  sql << mappings.collect { |token_type|
    quote_generic(token_type)
  }.join(', ')

  execute("#{sql};")
end
drop_text_search_dictionary(name, options = {}) click to toggle source

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/text_search.rb, line 163
def drop_text_search_dictionary(name, options = {})
  sql = 'DROP TEXT SEARCH DICTIONARY '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic_with_schema(name)
  sql << ' CASCADE' if options[:cascade]

  execute("#{sql};")
end
drop_text_search_parser(name, options = {}) click to toggle source

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/text_search.rb, line 275
def drop_text_search_parser(name, options = {})
  sql = 'DROP TEXT SEARCH PARSER '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic_with_schema(name)
  sql << ' CASCADE' if options[:cascade]

  execute("#{sql};")
end
drop_text_search_template(name, options = {}) click to toggle source

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/text_search.rb, line 227
def drop_text_search_template(name, options = {})
  sql = 'DROP TEXT SEARCH TEMPLATE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic_with_schema(name)
  sql << ' CASCADE' if options[:cascade]

  execute("#{sql};")
end
drop_trigger(name, table, options = {}) click to toggle source

Drops a trigger.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - cascades changes down to objects referring to the trigger.

# File lib/active_record/postgresql_extensions/triggers.rb, line 96
def drop_trigger(name, table, options = {})
  sql = 'DROP TRIGGER '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << "#{quote_generic(name)} ON #{quote_table_name(table)}"
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
drop_type(*args) click to toggle source

Drop TYPEs.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/types.rb, line 77
def drop_type(*args)
  options = args.extract_options!
  args.flatten!

  sql = 'DROP TYPE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |i| quote_generic(i) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
drop_user(*args)
Alias for: drop_role
drop_view(*args) click to toggle source

Drops a view.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.

# File lib/active_record/postgresql_extensions/views.rb, line 57
def drop_view(*args)
  options = args.extract_options!
  args.flatten!

  sql = 'DROP VIEW '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |v| quote_view_name(v) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
enable_event_trigger(name, options = {}) click to toggle source

Enables an event trigger.

Options

  • <tt>:replica

# File lib/active_record/postgresql_extensions/event_triggers.rb, line 58
def enable_event_trigger(name, options = {})
  if options[:always] && options[:replica]
    raise ArgumentError.new("Cannot use :replica and :always together when enabling an event trigger.")
  end

  sql = "ALTER EVENT TRIGGER #{quote_generic(name)} ENABLE"

  if options[:always]
    sql << ' ALWAYS'
  elsif options[:replica]
    sql << ' REPLICA'
  end

  execute "#{sql};"
end
enable_triggers(table, *triggers) click to toggle source

Enable triggers. If no triggers are specified, all triggers will be enabled.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 502
def enable_triggers(table, *triggers)
  quoted_table_name = quote_table_name(table)
  triggers = if triggers.present?
    triggers.collect { |trigger|
      case trigger
        when :all, :user
          trigger.to_s.upcase
        else
          quote_generic(trigger)
      end
    }
  else
    'ALL'
  end

  Array.wrap(triggers).each do |trigger|
    execute("ALTER TABLE #{quoted_table_name} ENABLE TRIGGER #{trigger};")
  end
end
enum_values(name) click to toggle source

Returns an Array of possible

# File lib/active_record/postgresql_extensions/types.rb, line 89
def enum_values(name)
  query(%{SELECT unnest(enum_range(NULL::#{quote_generic(name)}))}, 'Enum values').map(&:first)
end
extract_schema_and_table_names(name) click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 215
def extract_schema_and_table_names(name)
  if name.is_a?(Hash)
    [ name.keys.first.to_s, name.values.first.to_s ]
  else
    schema, name_part = extract_pg_identifier_from_name(name.to_s)

    unless name_part
      [ nil, schema.to_s ]
    else
      table_name, name_part = extract_pg_identifier_from_name(name_part)
      [ schema.to_s, table_name.to_s ]
    end
  end
end
extract_schema_name(name) click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 205
def extract_schema_name(name)
  schema, _ = extract_schema_and_table_names(name)
  schema if schema
end
extract_table_name(name) click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 210
def extract_table_name(name)
  _, name_part = extract_schema_and_table_names(name)
  name_part if name_part
end
foreign_keys(table_name, name = nil) click to toggle source

Returns an Array of foreign keys for a particular table. The Array itself is an Array of Arrays, where each particular Array contains the table being referenced, the foreign key and the name of the column in the referenced table.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 562
      def foreign_keys(table_name, name = nil)
        sql = PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL)
          SELECT
            confrelid::regclass AS referenced_table_name,
            a.attname AS foreign_key,
            af.attname AS referenced_column
          FROM
            pg_attribute af,
            pg_attribute a,
            pg_class c, (
              SELECT
                conrelid,
                confrelid,
                conkey[i] AS conkey,
                confkey[i] AS confkey
              FROM (
                SELECT
                  conrelid,
                  confrelid,
                  conkey,
                  confkey,
                  generate_series(1, array_upper(conkey, 1)) AS i
                FROM
                  pg_constraint
                WHERE
                  contype = 'f'
              ) ss
            ) ss2
          WHERE
            c.oid = conrelid
              AND
            c.relname = #{quote(table_name)}
              AND
            af.attnum = confkey
              AND
            af.attrelid = confrelid
              AND
            a.attnum = conkey
              AND
            a.attrelid = conrelid
          ;
        SQL

        query(sql, name).inject([]) do |memo, (tbl, column, referenced_column)|
          memo.tap {
            memo << {
              :table => tbl,
              :column => column,
              :referenced_column => referenced_column
            }
          }
        end
      end
grant_database_privileges(databases, privileges, roles, options = {}) click to toggle source

Grants privileges on databases. You can specify multiple databases, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLGrantPrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 33
def grant_database_privileges(databases, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :database, databases, privileges, roles, options).to_sql
end
grant_function_privileges(function_prototypes, privileges, roles, options = {}) click to toggle source

Grants privileges on functions. You can specify multiple functions, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLGrantPrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 41
def grant_function_privileges(function_prototypes, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :function, function_prototypes, privileges, roles, options, :quote_objects => false).to_sql
end
grant_language_privileges(languages, privileges, roles, options = {}) click to toggle source

Grants privileges on procedural languages. You can specify multiple languages, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLGrantPrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 49
def grant_language_privileges(languages, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :language, languages, privileges, roles, options).to_sql
end
grant_materialized_view_privileges(materialized_views, privileges, roles, options = {}) click to toggle source

Grants privileges on views. You can specify multiple materialized views, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLGrantPrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 81
def grant_materialized_view_privileges(materialized_views, privileges, roles, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:materialized_views)

  execute PostgreSQLGrantPrivilege.new(self, :materialized_view, materialized_views, privileges, roles, options, :named_object_type => false).to_sql
end
grant_role_membership(roles, role_names, options = {}) click to toggle source

Grants role membership to another role. You can specify multiple roles for both the roles and the role_names parameters using Arrays.

Options

  • :with_admin_option - adds the WITH ADMIN OPTION clause to the command.

# File lib/active_record/postgresql_extensions/permissions.rb, line 95
def grant_role_membership(roles, role_names, options = {})
  sql = "GRANT "
  sql << Array.wrap(roles).collect { |r| quote_role(r) }.join(', ')
  sql << ' TO '
  sql << Array.wrap(role_names).collect { |r| quote_role(r) }.join(', ')
  sql << ' WITH ADMIN OPTION' if options[:with_admin_option]
  execute("#{sql};")
end
grant_schema_privileges(schemas, privileges, roles, options = {}) click to toggle source

Grants privileges on schemas. You can specify multiple schemas, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLGrantPrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 57
def grant_schema_privileges(schemas, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :schema, schemas, privileges, roles, options, :ignore_schema => true).to_sql
end
grant_sequence_privileges(sequences, privileges, roles, options = {}) click to toggle source

Grants privileges on sequences. You can specify multiple sequences, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLGrantPrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 25
def grant_sequence_privileges(sequences, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :sequence, sequences, privileges, roles, options).to_sql
end
grant_table_privileges(tables, privileges, roles, options = {}) click to toggle source

Grants privileges on tables. You can specify multiple tables, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLGrantPrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 17
def grant_table_privileges(tables, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :table, tables, privileges, roles, options).to_sql
end
grant_tablespace_privileges(tablespaces, privileges, roles, options = {}) click to toggle source

Grants privileges on tablespaces. You can specify multiple tablespaces, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLGrantPrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 65
def grant_tablespace_privileges(tablespaces, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :tablespace, tablespaces, privileges, roles, options).to_sql
end
grant_view_privileges(views, privileges, roles, options = {}) click to toggle source

Grants privileges on views. You can specify multiple views, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLGrantPrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 73
def grant_view_privileges(views, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :view, views, privileges, roles, options, :named_object_type => false).to_sql
end
ignore_scoped_schema() { || ... } click to toggle source

When using with_schema, you can temporarily ignore the scoped schemas with ignore_block.

Example

with_schema :geospatial do
  create_table(:test) do |t|
    ignore_scoped_schema do
      t.integer(
        :ref_id,
        :references => {
          :table => :refs,
          :column => :id,
          :deferrable => true
        }
      )
    end
  end
end

# Produces:
#
# CREATE TABLE "geospatial"."test" (
#   "id" serial primary key,
#   "ref_id" integer DEFAULT NULL NULL,
#   FOREIGN KEY ("ref_id") REFERENCES "refs" ("id")
# )

Here we see that we used the geospatial schema when naming the test table and dropped back to not specifying a schema when setting up the foreign key to the refs table. If we had not used ignore_scoped_schema, the foreign key would have been defined thusly:

FOREIGN KEY ("ref_id") REFERENCES "geospatial"."refs" ("id")
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 68
def ignore_scoped_schema
  with_schema nil do
    yield
  end
end
ignored_tables(name = nil) click to toggle source

Returns an Array of tables to ignore.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 455
      def ignored_tables(name = nil)
        query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map { |row| row[0] }
          SELECT tablename
          FROM pg_tables
          WHERE schemaname IN ('pg_catalog');
        SQL
      end
language_exists?(name) click to toggle source
# File lib/active_record/postgresql_extensions/languages.rb, line 75
def language_exists?(name)
  languages.include?(name.to_s)
end
languages(name = nil) click to toggle source

Returns an Array of available languages.

# File lib/active_record/postgresql_extensions/languages.rb, line 68
      def languages(name = nil)
        query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map { |row| row[0] }
          SELECT lanname
          FROM pg_language;
        SQL
      end
original_change_table(table_name, options = {})
Alias for: change_table
original_create_table(table_name, options = {})
Alias for: create_table
original_drop_table(*args)
Alias for: drop_table
original_rename_table(name, new_name, options = {})
Alias for: rename_table
quote_function(name) click to toggle source

Quoting method for server-side functions.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 153
def quote_function(name)
  quote_generic_with_schema(name)
end
quote_generic(g) click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 88
def quote_generic(g)
  quote_column_name(g)
end
quote_generic_ignore_scoped_schema(g) click to toggle source

A generic quoting method for PostgreSQL that specifically ignores any and all schemas.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 99
def quote_generic_ignore_scoped_schema(g)
  if g.is_a?(Hash)
    quote_generic g.values.first
  else
    quote_generic g
  end
end
quote_generic_with_schema(g) click to toggle source

A generic quoting method for PostgreSQL with our special schema support.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 109
def quote_generic_with_schema(g)
  if g.is_a?(Hash)
    "#{quote_schema(g.keys.first)}.#{quote_generic(g.values.first)}"
  else
    if current_scoped_schema
      quote_schema(current_scoped_schema) << '.'
    end.to_s << quote_generic(g)
  end
end
quote_language(language) click to toggle source

Quoting method for procedural languages.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 130
def quote_language(language)
  quote_generic(language)
end
quote_role(role) click to toggle source

Quoting method for roles.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 120
def quote_role(role)
  quote_generic(role)
end
quote_rule(rule) click to toggle source

Quoting method for rules.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 125
def quote_rule(rule)
  quote_generic(rule)
end
quote_schema(schema) click to toggle source

Quoting method for schemas. When the schema is :public or ‘public’ or some form thereof, we’ll convert that to “PUBLIC” without quoting.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 137
def quote_schema(schema)
  if schema.to_s.upcase == 'PUBLIC'
    'PUBLIC'
  else
    quote_generic(schema)
  end
end
quote_sequence(name) click to toggle source

Quoting method for sequences. This really just goes to the quoting method for table names, as sequences can belong to specific schemas.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 148
def quote_sequence(name)
  quote_generic_with_schema(name)
end
quote_table_name_with_schemas(name) click to toggle source

Quoting method for table names. This method has been extended beyond the standard ActiveRecord quote_table_name to allow for

  • scoped schema support with with_schema. When using with_schema, table names will be prefixed with the current scoped schema name.

  • you can specify a specific schema using a Hash containing a single value pair where the key is the schema name and the key is the table name.

Example of using a Hash as a table name:

quote_table_name(:geospatial => :epois) # => "geospatial"."epois"
# => "geospatial"."epois"

quote_table_name(:epois)
# => "epois"

with_schema(:geospatial) { quote_table_name(:epois) }
# => "geospatial"."epois"

with_schema(:geospatial) do
  ignore_scoped_schema do
    quote_table_name(:epois)
  end
end
# => "epois"
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 184
def quote_table_name_with_schemas(name)
  if current_scoped_schema || name.is_a?(Hash)
    quote_generic_with_schema(name)
  else
    quote_table_name_without_schemas(name)
  end
end
quote_tablespace(name) click to toggle source

Quoting method for tablespaces.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 201
def quote_tablespace(name)
  quote_generic(name)
end
quote_view_name(name) click to toggle source

Quoting method for view names. This really just goes to the quoting method for table names, as views can belong to specific schemas.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 196
def quote_view_name(name)
  quote_table_name(name)
end
referenced_foreign_keys(table_name, name = nil) click to toggle source

Returns an Array of foreign keys that point to a particular table. The Array itself is an Array of Arrays, where each particular Array contains the referencing table, the foreign key and the name of the column in the referenced table.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 620
      def referenced_foreign_keys(table_name, name = nil)
        sql = PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL)
          SELECT
            c2.relname AS table_name,
            a.attname AS foreign_key,
            af.attname AS referenced_column
          FROM
            pg_attribute af,
            pg_attribute a,
            pg_class c1,
            pg_class c2, (
              SELECT
                conrelid,
                confrelid,
                conkey[i] AS conkey,
                confkey[i] AS confkey
              FROM (
                SELECT
                  conrelid,
                  confrelid,
                  conkey,
                  confkey,
                  generate_series(1, array_upper(conkey, 1)) AS i
                FROM
                  pg_constraint
                WHERE
                  contype = 'f'
              ) ss
            ) ss2
          WHERE
            confrelid = c1.oid
              AND
            conrelid = c2.oid
              AND
            c1.relname = #{quote(table_name)}
              AND
            af.attnum = confkey
              AND
            af.attrelid = confrelid
              AND
            a.attnum = conkey
              AND
            a.attrelid = conrelid
          ;
        SQL

        query(sql, name).inject([]) do |memo, (tbl, column, referenced_column)|
          memo.tap {
            memo << {
              :table => tbl,
              :column => column,
              :referenced_column => referenced_column
            }
          }
        end
      end
refresh_materialized_view(name, options = {}) click to toggle source

Refreshes the data in a materialized view.

Options

  • :with_data - whether to populate the materialized view with data. The default is true.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 143
def refresh_materialized_view(name, options = {})
  options = {
    :with_data => true
  }.merge(options)

  sql = "REFRESH MATERIALIZED VIEW #{quote_view_name(name)}"
  sql << " WITH NO DATA" unless options[:with_data]

  execute "#{sql};"
end
remove_cluster_from_materialized_view(name) click to toggle source

Remove a cluster from materialized view.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 131
def remove_cluster_from_materialized_view(name)
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :remove_cluster => true
  }).to_sql
end
rename_event_trigger(name, new_name) click to toggle source

Renames an event trigger.

# File lib/active_record/postgresql_extensions/event_triggers.rb, line 44
def rename_event_trigger(name, new_name)
  execute "ALTER EVENT TRIGGER #{quote_generic(name)} RENAME TO #{quote_generic(new_name)};"
end
rename_function(name, *args) click to toggle source

Renames a function.

# File lib/active_record/postgresql_extensions/functions.rb, line 175
def rename_function(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(1, 2)

  options = args.extract_options!
  rename_to = args.pop
  arguments = args.pop

  execute PostgreSQLFunctionAlterer.new(self, name, arguments, :rename_to => rename_to).to_s
end
rename_index(name, new_name, options = {}) click to toggle source

Renames an index.

# File lib/active_record/postgresql_extensions/indexes.rb, line 156
def rename_index(name, new_name, options = {})
  execute "ALTER INDEX #{quote_generic(name)} RENAME TO #{quote_generic(new_name)};"
end
rename_materialized_view(name, new_name, options = {}) click to toggle source

Renames a materialized view.

# File lib/active_record/postgresql_extensions/materialized_views.rb, line 68
def rename_materialized_view(name, new_name, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :rename_to => new_name
  }, options).to_sql
end
rename_rule(old_name, table, new_name) click to toggle source

Renames a rule. Available in PostgreSQL 9.3+.

# File lib/active_record/postgresql_extensions/rules.rb, line 58
def rename_rule(old_name, table, new_name)
  execute "ALTER RULE #{quote_rule(old_name)} ON #{quote_table_name(table)} RENAME TO #{quote_rule(new_name)};"
end
rename_sequence(name, rename, options = {}) click to toggle source

Renames the sequence.

# File lib/active_record/postgresql_extensions/sequences.rb, line 78
def rename_sequence(name, rename, options = {})
  execute("ALTER SEQUENCE #{quote_sequence(name)} RENAME TO #{quote_generic_ignore_scoped_schema(rename)};")
end
rename_table(name, new_name, options = {}) click to toggle source

Renames a table. We’re overriding the original rename_table so that we can take advantage of our super schema quoting capabilities. You can still access the original method via original_rename_table.

# File lib/active_record/postgresql_extensions/tables.rb, line 164
def rename_table(name, new_name, options = {})
  execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_generic_ignore_scoped_schema(new_name)};"
end
Also aliased as: original_rename_table
rename_tablespace(old_name, new_name) click to toggle source

Renames a tablespace.

# File lib/active_record/postgresql_extensions/tablespaces.rb, line 39
def rename_tablespace(old_name, new_name)
  execute("ALTER TABLESPACE #{quote_tablespace(old_name)} RENAME TO #{quote_tablespace(new_name)};")
end
rename_text_search_configuration(old_name, new_name) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 113
def rename_text_search_configuration(old_name, new_name)
  execute("ALTER TEXT SEARCH CONFIGURATION %s RENAME TO %s;" % [
    quote_generic_with_schema(old_name),
    quote_generic_with_schema(new_name)
  ])
end
rename_text_search_dictionary(old_name, new_name) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 186
def rename_text_search_dictionary(old_name, new_name)
  execute("ALTER TEXT SEARCH DICTIONARY %s RENAME TO %s;" % [
    quote_generic_with_schema(old_name),
    quote_generic_with_schema(new_name)
  ])
end
rename_text_search_parser(old_name, new_name) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 284
def rename_text_search_parser(old_name, new_name)
  execute("ALTER TEXT SEARCH PARSER %s RENAME TO %s;" % [
    quote_generic_with_schema(old_name),
    quote_generic_with_schema(new_name)
  ])
end
rename_text_search_template(old_name, new_name) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 236
def rename_text_search_template(old_name, new_name)
  execute("ALTER TEXT SEARCH TEMPLATE %s RENAME TO %s;" % [
    quote_generic_with_schema(old_name),
    quote_generic_with_schema(new_name)
  ])
end
rename_trigger(name, table, new_name, options = {}) click to toggle source

Renames a trigger.

# File lib/active_record/postgresql_extensions/triggers.rb, line 105
def rename_trigger(name, table, new_name, options = {})
  execute "ALTER TRIGGER #{quote_generic(name)} ON #{quote_table_name(table)} RENAME TO #{quote_generic(new_name)};"
end
rename_view(name, new_name, options = {}) click to toggle source

Renames a view.

# File lib/active_record/postgresql_extensions/views.rb, line 69
def rename_view(name, new_name, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :rename_to => new_name
  }, options).to_sql
end
replace_text_search_configuration_dictionary(name, old_dictionary, new_dictionary) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 70
def replace_text_search_configuration_dictionary(name, old_dictionary, new_dictionary)
  sql = "ALTER TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} ALTER MAPPING REPLACE "
  sql << "#{quote_generic(old_dictionary)} WITH #{quote_generic(new_dictionary)}"

  execute("#{sql};")
end
reset_role() click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 441
def reset_role
  execute('RESET ROLE;')
end
reset_tablespace_parameters(tablespace, *parameters) click to toggle source
# File lib/active_record/postgresql_extensions/tablespaces.rb, line 61
def reset_tablespace_parameters(tablespace, *parameters)
  sql = "ALTER TABLESPACE #{quote_tablespace(tablespace)} RESET ("

  sql << parameters.flatten.collect { |k|
    assert_valid_tablespace_parameter(k)
    "\n  #{quote_generic(k)}"
  }.join(",")

  sql << "\n);"

  execute(sql)
end
revoke_database_privileges(databases, privileges, roles, options = {}) click to toggle source

Revokes database privileges. You can specify multiple databases, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLRevokePrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 124
def revoke_database_privileges(databases, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :database, databases, privileges, roles, options).to_sql
end
revoke_function_privileges(function_prototypes, privileges, roles, options = {}) click to toggle source

Revokes function privileges. You can specify multiple functions, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLRevokePrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 132
def revoke_function_privileges(function_prototypes, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :function, function_prototypes, privileges, roles, options, :quote_objects => false).to_sql
end
revoke_language_privileges(languages, privileges, roles, options = {}) click to toggle source

Revokes language privileges. You can specify multiple languages, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLRevokePrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 140
def revoke_language_privileges(languages, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :language, languages, privileges, roles, options).to_sql
end
revoke_materialized_view_privileges(materialized_views, privileges, roles, options = {}) click to toggle source

Revokes materialized view privileges. You can specify multiple materialized views, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLRevokePrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 164
def revoke_materialized_view_privileges(materialized_views, privileges, roles, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:materialized_views)

  execute PostgreSQLRevokePrivilege.new(self, :materialized_view, materialized_views, privileges, roles, options, :named_object_type => false).to_sql
end
revoke_role_membership(roles, role_names, options = {}) click to toggle source

Revokes role membership. You can specify multiple roles for both the roles and the role_names parameters using Arrays.

Options

  • :with_admin_option - adds the WITH ADMIN OPTION clause to the command.

  • :cascade - adds the CASCADE option to the command.

# File lib/active_record/postgresql_extensions/permissions.rb, line 187
def revoke_role_membership(roles, role_names, options = {})
  sql = 'REVOKE '
  sql << 'ADMIN_OPTION_FOR ' if options[:admin_option_for]
  sql << Array.wrap(roles).collect { |r| quote_role(r) }.join(', ')
  sql << ' FROM '
  sql << Array.wrap(role_names).collect { |r| quote_role(r) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end
revoke_schema_privileges(schemas, privileges, roles, options = {}) click to toggle source

Revokes schema privileges. You can specify multiple schemas, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLRevokePrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 148
def revoke_schema_privileges(schemas, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :schema, schemas, privileges, roles, options, :ignore_schema => true).to_sql
end
revoke_sequence_privileges(sequences, privileges, roles, options = {}) click to toggle source

Revokes sequence privileges. You can specify multiple sequences, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLRevokePrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 116
def revoke_sequence_privileges(sequences, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :sequence, sequences, privileges, roles, options).to_sql
end
revoke_table_privileges(tables, privileges, roles, options = {}) click to toggle source

Revokes table privileges. You can specify multiple tables, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLRevokePrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 108
def revoke_table_privileges(tables, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :table, tables, privileges, roles, options).to_sql
end
revoke_tablespace_privileges(tablespaces, privileges, roles, options = {}) click to toggle source

Revokes tablespace privileges. You can specify multiple tablespaces, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLRevokePrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 156
def revoke_tablespace_privileges(tablespaces, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :tablespace, tablespaces, privileges, roles, options).to_sql
end
revoke_view_privileges(views, privileges, roles, options = {}) click to toggle source

Revokes view privileges. You can specify multiple views, roles and privileges all at once using Arrays for each of the desired parameters. See PostgreSQLRevokePrivilege for usage.

# File lib/active_record/postgresql_extensions/permissions.rb, line 174
def revoke_view_privileges(views, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :view, views, privileges, roles, options, :named_object_type => false).to_sql
end
role_exists?(name) click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 420
def role_exists?(name)
  roles.include?(name)
end
roles(name = nil) click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 413
      def roles(name = nil)
        query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map { |row| row[0] }
          SELECT rolname
          FROM pg_roles
        SQL
      end
schema_search_path_with_csv_fix() click to toggle source

Fix ActiveRecord bug when grabbing the current search_path.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 478
def schema_search_path_with_csv_fix
  @schema_search_path ||= query('SHOW search_path;')[0][0].gsub(/,\s+/, ',')
end
scoped_schemas() click to toggle source

See what the current scoped schemas are. Should be thread-safe if using the PostgreSQL adapter’s concurrency mode.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 76
def scoped_schemas
  scoped_schemas = (Thread.current[:scoped_schemas] ||= {})
  scoped_schemas[self] ||= []
end
sequence_exists?(name) click to toggle source
# File lib/active_record/postgresql_extensions/sequences.rb, line 129
def sequence_exists?(name)
  sequences.include?(name.to_s)
end
sequences(name = nil) click to toggle source

Returns an Array of available sequences.

# File lib/active_record/postgresql_extensions/sequences.rb, line 121
      def sequences(name = nil)
        query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map { |row| row[0] }
          SELECT c.relname AS sequencename
          FROM pg_class c
          WHERE c.relkind = 'S'::"char";
        SQL
      end
set_role(role, options = {}) click to toggle source

Sets the current database role/user. The :duration option can be set to :session or :local as described in the PostgreSQL docs.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 426
def set_role(role, options = {})
  duration = if options[:duration]
    if [ :session, :local ].include?(options[:duration])
      options[:duration].to_s.upcase
    else
      raise ArgumentError.new("The :duration option must be one of :session or :local.")
    end
  end

  sql = 'SET '
  sql << "#{duration} " if duration
  sql << "ROLE #{quote_role(role)};"
  execute(sql, "Setting current role")
end
set_sequence_value(name, value, options = {}) click to toggle source

Calls the setval function on the sequence.

Options

  • :is_called - the value to set in the third argument to the function call, which is, appropriately enough, the is_called argument. The default value is true.

# File lib/active_record/postgresql_extensions/sequences.rb, line 106
def set_sequence_value(name, value, options = {})
  options = {
    :is_called => true
  }.merge(options)

  execute "SELECT setval(#{quote(name)}, #{value.to_i}, " <<
    if options[:is_called]
      'true'
    else
      'false'
    end <<
    ');'
end
type_exists?(name) click to toggle source
# File lib/active_record/postgresql_extensions/types.rb, line 31
def type_exists?(name)
  types.include?(name.to_s)
end
types(name = nil) click to toggle source

Returns an Array of available languages.

# File lib/active_record/postgresql_extensions/types.rb, line 11
      def types(name = nil)
        query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map(&:first)
          SELECT t.typname as type
            FROM pg_type t
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
            WHERE (t.typrelid = 0 OR (
              SELECT c.relkind = 'c'
                FROM pg_catalog.pg_class c
                WHERE c.oid = t.typrelid
            )) AND
              NOT EXISTS(
                SELECT 1
                  FROM pg_catalog.pg_type el
                  WHERE el.oid = t.typelem
                    AND el.typarray = t.oid
              ) AND
              n.nspname NOT IN ('information_schema');
        SQL
      end
update_extension(name, new_version = nil) click to toggle source
# File lib/active_record/postgresql_extensions/extensions.rb, line 40
def update_extension(name, new_version = nil)
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

  sql = "ALTER EXTENSION #{quote_generic(name)} UPDATE"
  sql << " TO #{quote_generic(new_version)}" if new_version;
  execute("#{sql};")
end
update_geometry_srid(table_name, column_name, srid) click to toggle source

Updates the definition of a geometry field to a new SRID value.

# File lib/active_record/postgresql_extensions/geometry.rb, line 32
def update_geometry_srid(table_name, column_name, srid)
  schema, table = extract_schema_and_table_names(table_name)

  args = [
    quote(table),
    quote(column_name),
    quote(srid)
  ]

  args.unshift(quote(schema)) if schema

  execute(%{SELECT UpdateGeometrySRID(#{args.join(', ')});})
end
vacuum(*args) click to toggle source

VACUUMs a database, table or columns on a table. See PostgreSQLVacuum for details.

# File lib/active_record/postgresql_extensions/vacuum.rb, line 9
def vacuum(*args)
  vacuumer = PostgreSQLVacuum.new(self, *args)
  execute("#{vacuumer};")
end
validate_constraint(table, name) click to toggle source

Validates a constraint and removes the NOT VALID clause from its definition.

# File lib/active_record/postgresql_extensions/constraints.rb, line 78
def validate_constraint(table, name)
  execute("ALTER TABLE #{quote_table_name(table)} VALIDATE CONSTRAINT #{quote_generic(name)};")
end
view_exists?(name) click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 391
      def view_exists?(name)
        name         = name.to_s
        schema, view = name.split('.', 2)

        unless view # A view was provided without a schema
          view  = schema
          schema = nil
        end

        if name =~ /^"/ # Handle quoted view names
          view  = name
          schema = nil
        end

        query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL)).first[0].to_i > 0
          SELECT COUNT(*)
          FROM pg_views
          WHERE viewname = '#{view.gsub(/(^"|"$)/,'')}'
          #{schema ? "AND schemaname = '#{schema}'" : ''}
        SQL
      end
views(name = nil) click to toggle source

Returns an Array of database views.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 383
      def views(name = nil)
        query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map { |row| row[0] }
          SELECT viewname
          FROM pg_views
          WHERE schemaname = ANY (current_schemas(false))
        SQL
      end
with_schema(schema) { || ... } click to toggle source

with_schema is kind of like with_scope. It wraps various object names in SQL statements into a PostgreSQL schema. You can have multiple with_schemas wrapped around each other, and hopefully they won’t collide with one another.

Examples

# should produce '"geospatial"."my_tables"'
with_schema :geospatial do
  quote_table_name('my_table')
end

# should produce 'SELECT * FROM "geospatial"."models"'
with_schema :geospatial do
  Model.find(:all)
end
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 24
def with_schema(schema)
  scoped_schemas << schema
  begin
    yield
  ensure
    scoped_schemas.pop
  end
end
without_triggers(table, *triggers) { || ... } click to toggle source

Temporarily disable triggers. If no triggers are specified, all triggers will be disabled. You can specify ALL or USER triggers by using the symbols :all or :user. If you have actual triggers named “all” or “user”, use Strings instead of Symbols.

# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 551
def without_triggers(table, *triggers)
  disable_triggers(table, *triggers)
  yield
ensure
  enable_triggers(table, *triggers)
end

Private Instance Methods

assert_valid_add_enum_value_options(options) click to toggle source
# File lib/active_record/postgresql_extensions/types.rb, line 94
def assert_valid_add_enum_value_options(options)
  if options[:before] && options[:after]
    raise InvalidAddEnumValueOptions.new("Can't use both :before and :after options together")
  end
end
assert_valid_copy_from_options(options) click to toggle source
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 756
def assert_valid_copy_from_options(options)
  if options[:program] && !ActiveRecord::PostgreSQLExtensions::Features.copy_from_program?
    raise InvalidCopyFromOptions.new("The :program option is only available in PostgreSQL 9.3+.")
  end

  if options[:freeze] && !ActiveRecord::PostgreSQLExtensions::Features.copy_from_freeze?
    raise InvalidCopyFromOptions.new("The :freeze option is only available in PostgreSQL 9.3+.")
  end

  if options[:encoding] && !ActiveRecord::PostgreSQLExtensions::Features.copy_from_encoding?
    raise InvalidCopyFromOptions.new("The :encoding option is only available in PostgreSQL 9.1+.")
  end
end
assert_valid_on_commit(temp, on_commit) click to toggle source
# File lib/active_record/postgresql_extensions/tables.rb, line 171
def assert_valid_on_commit(temp, on_commit)
  unless on_commit.nil?
    if !ON_COMMIT_VALUES.include?(on_commit.to_s.downcase)
      raise ActiveRecord::InvalidTableOptions.new("Invalid ON COMMIT value - #{on_commit}")
    elsif !temp
      raise ActiveRecord::InvalidTableOptions.new("ON COMMIT can only be used with temporary tables")
    end
  end
end
assert_valid_tablespace_parameter(parameter) click to toggle source
# File lib/active_record/postgresql_extensions/tablespaces.rb, line 77
def assert_valid_tablespace_parameter(parameter)
  if !TABLESPACE_PARAMETERS.include? parameter.to_s.downcase
    raise ActiveRecord::InvalidTablespaceParameter.new(parameter)
  end
end
extract_hash_or_array_options(hash_or_array, *keys) click to toggle source
# File lib/active_record/postgresql_extensions/text_search.rb, line 296
def extract_hash_or_array_options(hash_or_array, *keys)
  case v = hash_or_array[0]
    when Hash
      if (keys - (sliced = v.slice(*keys)).keys).length == 0
        keys.collect do |k|
          sliced[k]
        end
      else
        [ v.keys.first, v.values.first ]
      end
    else
      v = hash_or_array.flatten
      [ v.shift, *v ]
  end
end