class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
Constants
- ON_COMMIT_VALUES
- TABLESPACE_PARAMETERS
Public Instance Methods
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
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
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
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
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
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
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
# 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
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
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
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
# 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
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
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
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
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
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
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
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
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
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 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
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
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
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
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
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
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
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
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
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
# 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
# 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
# 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
# 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
# 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
# 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
# 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
# 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
# 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
# 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
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
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
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 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
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
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
# 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
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
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 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
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
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
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
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
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 anOR 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 likesearch_path
ortime 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 actualFROM CURRENT
clause. -
:body
- allows you to set a function body when the arguments tocreate_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
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. Withcreate_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 thetext_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
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 thepg_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
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 byquery
. -
: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
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
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 anOR REPLACE
clause to the command. -
:conditions
- aWHERE
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
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 withalter_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
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 isnil
orfalse
, 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
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 thedrop_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
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
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
# 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
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
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
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
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 byquery
. -
: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
# 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
Get the current scoped schema.
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 82 def current_scoped_schema scoped_schemas.last end
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. 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
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
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
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
Drops a function.
Options¶ ↑
-
:if_exists
- adds anIF 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
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
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
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 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
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
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
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
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
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
# 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
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
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
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
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 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
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
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. 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
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
# 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
# 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
# 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
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
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
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
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
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
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
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
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
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
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
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
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
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
# File lib/active_record/postgresql_extensions/languages.rb, line 75 def language_exists?(name) languages.include?(name.to_s) end
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
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
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 88 def quote_generic(g) quote_column_name(g) end
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
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
Quoting method for procedural languages.
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 130 def quote_language(language) quote_generic(language) end
Quoting method for roles.
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 120 def quote_role(role) quote_generic(role) end
Quoting method for rules.
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 125 def quote_rule(rule) quote_generic(rule) end
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
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
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
Quoting method for tablespaces.
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 201 def quote_tablespace(name) quote_generic(name) end
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
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
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 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
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
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
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
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
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
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
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
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
# 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
# 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
# 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
# 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
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
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
# 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
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 441 def reset_role execute('RESET ROLE;') end
# 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
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
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
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
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
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
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
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
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
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
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
# File lib/active_record/postgresql_extensions/adapter_extensions.rb, line 420 def role_exists?(name) roles.include?(name) end
# 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
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
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
# File lib/active_record/postgresql_extensions/sequences.rb, line 129 def sequence_exists?(name) sequences.include?(name.to_s) end
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
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
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, theis_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
# File lib/active_record/postgresql_extensions/types.rb, line 31 def type_exists?(name) types.include?(name.to_s) end
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
# 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
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
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
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
# 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
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
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
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
# 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
# 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
# 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
# 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
# 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