Class: ActiveRecord::ConnectionAdapters::PostgreSQLAdapter

Inherits:
Object
  • Object
show all
Defined in:
lib/active_record/postgresql_extensions/roles.rb,
lib/active_record/postgresql_extensions/rules.rb,
lib/active_record/postgresql_extensions/types.rb,
lib/active_record/postgresql_extensions/views.rb,
lib/active_record/postgresql_extensions/tables.rb,
lib/active_record/postgresql_extensions/vacuum.rb,
lib/active_record/postgresql_extensions/indexes.rb,
lib/active_record/postgresql_extensions/schemas.rb,
lib/active_record/postgresql_extensions/geometry.rb,
lib/active_record/postgresql_extensions/triggers.rb,
lib/active_record/postgresql_extensions/functions.rb,
lib/active_record/postgresql_extensions/languages.rb,
lib/active_record/postgresql_extensions/sequences.rb,
lib/active_record/postgresql_extensions/extensions.rb,
lib/active_record/postgresql_extensions/constraints.rb,
lib/active_record/postgresql_extensions/permissions.rb,
lib/active_record/postgresql_extensions/tablespaces.rb,
lib/active_record/postgresql_extensions/text_search.rb,
lib/active_record/postgresql_extensions/event_triggers.rb,
lib/active_record/postgresql_extensions/adapter_extensions.rb,
lib/active_record/postgresql_extensions/materialized_views.rb

Instance Method Summary collapse

Instance Method Details

#add_check_constraint(table, expression, options = {}) ⇒ Object

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



33
34
35
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 33

def add_check_constraint(table, expression, options = {})
  add_constraint(table, PostgreSQLCheckConstraint.new(self, expression, options))
end

#add_column_options_with_expression!(sql, options) ⇒ Object

:nodoc:



707
708
709
710
711
712
713
714
715
716
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 707

def add_column_options_with_expression!(sql, options) #:nodoc:
  if options_include_default?(options) &&
    options[:default].is_a?(Hash) &&
    options[:default].has_key?(:expression)

    expression = options.delete(:default)
    sql << " DEFAULT #{expression[:expression]}"
  end
  add_column_options_without_expression!(sql, options)
end

#add_constraint(table, constraint) ⇒ Object

Adds a generic constraint.



27
28
29
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 27

def add_constraint(table, constraint)
  execute("ALTER TABLE #{quote_table_name(table)} ADD #{constraint};")
end

#add_enum_value(enum, value, options = {}) ⇒ Object

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+.



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/active_record/postgresql_extensions/types.rb', line 49

def add_enum_value(enum, value, options = {})
  assert_valid_add_enum_value_options(options)

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

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

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

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

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

  execute("#{sql};")
end

#add_exclude_constraint(table, excludes, options = {}) ⇒ Object

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



52
53
54
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 52

def add_exclude_constraint(table, excludes, options = {})
  add_constraint(table, PostgreSQLExcludeConstraint.new(self, table, excludes, options))
end

#add_foreign_key_constraint(table, columns, ref_table, *args) ⇒ Object Also known as: add_foreign_key

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



45
46
47
 
# 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

#add_or_alter_text_search_configuration_mapping(name, tokens, dictionaries, options = {}) ⇒ Object

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.



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 45

def add_or_alter_text_search_configuration_mapping(name, tokens, dictionaries, options = {})
  options = {
    :action => :add
  }.merge(options)

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

  add_or_alter = options[:action].to_s.upcase

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

  sql << ' WITH '

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

  execute("#{sql};")
end

#add_primary_key_constraint(table, columns, options = {}) ⇒ Object Also known as: add_primary_key

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



58
59
60
 
# 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

#add_text_search_configuration_mapping(name, tokens, dictionaries) ⇒ Object 



31
32
33
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 31

def add_text_search_configuration_mapping(name, tokens, dictionaries)
  add_or_alter_text_search_configuration_mapping(name, tokens, dictionaries, :action => :add)
end

#add_unique_constraint(table, columns, options = {}) ⇒ Object

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



39
40
41
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 39

def add_unique_constraint(table, columns, options = {})
  add_constraint(table, PostgreSQLUniqueConstraint.new(self, columns, options))
end

#alter_event_trigger_owner(name, role) ⇒ Object

Reassigns ownership of an event trigger.



49
50
51
 
# File 'lib/active_record/postgresql_extensions/event_triggers.rb', line 49

def alter_event_trigger_owner(name, role)
  execute "ALTER EVENT TRIGGER #{quote_generic(name)} OWNER TO #{quote_generic(role)};"
end

#alter_extension(name, options = {}) ⇒ Object

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.



122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/active_record/postgresql_extensions/extensions.rb', line 122

def alter_extension(name, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

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

  if block_given?
    yield alterer
  end

  execute(alterer.to_s) unless alterer.empty?
end

#alter_extension_schema(name, schema) ⇒ Object 



48
49
50
51
52
 
# File 'lib/active_record/postgresql_extensions/extensions.rb', line 48

def alter_extension_schema(name, schema)
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

  execute "ALTER EXTENSION #{quote_generic(name)} SET SCHEMA #{quote_schema(schema)};"
end

#alter_function(name, *args) ⇒ Object

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";

Raises:

  • (ArgumentError) 


236
237
238
239
240
241
242
243
244
245
246
247
248
 
# File 'lib/active_record/postgresql_extensions/functions.rb', line 236

def alter_function(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(0, 2)

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

  if block_given?
    yield alterer
  end

  execute alterer.to_s unless alterer.empty?
end

#alter_function_owner(name, *args) ⇒ Object

Changes the function’s owner.

Raises:

  • (ArgumentError) 


186
187
188
189
190
191
192
193
194
 
# File 'lib/active_record/postgresql_extensions/functions.rb', line 186

def alter_function_owner(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(1, 2)

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

  execute PostgreSQLFunctionAlterer.new(self, name, arguments, :owner_to => owner_to).to_s
end

#alter_function_schema(name, *args) ⇒ Object

Changes the function’s schema.

Raises:

  • (ArgumentError) 


197
198
199
200
201
202
203
204
205
 
# File 'lib/active_record/postgresql_extensions/functions.rb', line 197

def alter_function_schema(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(1, 2)

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

  execute PostgreSQLFunctionAlterer.new(self, name, arguments, :set_schema => set_schema).to_s
end

#alter_index_tablespace(name, tablespace, options = {}) ⇒ Object

Changes an index’s tablespace.



161
162
163
 
# File 'lib/active_record/postgresql_extensions/indexes.rb', line 161

def alter_index_tablespace(name, tablespace, options = {})
  execute "ALTER INDEX #{quote_generic(name)} SET TABLESPACE #{quote_tablespace(tablespace)};"
end

#alter_language_name(old_language, new_language, options = {}) ⇒ Object

Renames a language.



58
59
60
 
# File 'lib/active_record/postgresql_extensions/languages.rb', line 58

def alter_language_name(old_language, new_language, options = {})
  execute "ALTER PROCEDURAL LANGUAGE #{quote_language(old_language)} RENAME TO #{quote_language(new_language)};"
end

#alter_language_owner(language, role, options = {}) ⇒ Object

Changes a language’s owner.



63
64
65
 
# File 'lib/active_record/postgresql_extensions/languages.rb', line 63

def alter_language_owner(language, role, options = {})
  execute "ALTER PROCEDURAL LANGUAGE #{quote_language(language)} OWNER TO #{quote_role(role)};"
end

#alter_materialized_view_drop_column_default(name, column, options = {}) ⇒ Object

Drop the default value on a materialized view column



87
88
89
90
91
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 87

def alter_materialized_view_drop_column_default(name, column, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :drop_default => column
  }, options).to_sql
end

#alter_materialized_view_owner(name, role, options = {}) ⇒ Object

Change the ownership of a materialized view.



94
95
96
97
98
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 94

def alter_materialized_view_owner(name, role, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :owner_to => role
  }, options).to_sql
end

#alter_materialized_view_reset_options(name, *args) ⇒ Object

Resets a materialized view’s options.



115
116
117
118
119
120
121
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 115

def alter_materialized_view_reset_options(name, *args)
  options = args.extract_options!

  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :reset_options => args
  }, options).to_sql
end

#alter_materialized_view_schema(name, schema, options = {}) ⇒ Object

Alter a materialized view’s schema.



101
102
103
104
105
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 101

def alter_materialized_view_schema(name, schema, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :set_schema => schema
  }, options).to_sql
end

#alter_materialized_view_set_column_default(name, column, default, options = {}) ⇒ Object

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.



79
80
81
82
83
84
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 79

def alter_materialized_view_set_column_default(name, column, default, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :column => column,
    :set_default => default
  }, options).to_sql
end

#alter_materialized_view_set_options(name, set_options, options = {}) ⇒ Object

Sets a materialized view’s options using a Hash.



108
109
110
111
112
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 108

def alter_materialized_view_set_options(name, set_options, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :set_options => set_options
  }, options).to_sql
end

#alter_role(name, options = {}) ⇒ Object Also known as: alter_user

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



20
21
22
 
# 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_schema_name(old_schema, new_schema) ⇒ Object

Alter’s a schema’s name.



52
53
54
 
# File 'lib/active_record/postgresql_extensions/schemas.rb', line 52

def alter_schema_name(old_schema, new_schema)
  execute("ALTER SCHEMA #{quote_schema(old_schema)} RENAME TO #{quote_schema(new_schema)};")
end

#alter_schema_owner(schema, role) ⇒ Object

Changes a schema’s owner.



57
58
59
 
# File 'lib/active_record/postgresql_extensions/schemas.rb', line 57

def alter_schema_owner(schema, role)
  execute("ALTER SCHEMA #{quote_schema(schema)} OWNER TO #{quote_role(role)};")
end

#alter_sequence(name, options = {}) ⇒ Object

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.



95
96
97
 
# File 'lib/active_record/postgresql_extensions/sequences.rb', line 95

def alter_sequence(name, options = {})
  execute(PostgreSQLSequenceDefinition.new(self, :alter, name, options).to_s)
end

#alter_sequence_schema(name, schema, options = {}) ⇒ Object

Alters the sequence’s schema.



83
84
85
 
# File 'lib/active_record/postgresql_extensions/sequences.rb', line 83

def alter_sequence_schema(name, schema, options = {})
  execute("ALTER SEQUENCE #{quote_sequence(name)} SET SCHEMA #{quote_schema(schema)};")
end

#alter_table_schema(table_name, schema, options = {}) ⇒ Object

Set the schema of a table.



17
18
19
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 17

def alter_table_schema(table_name, schema, options = {})
  execute "ALTER TABLE #{quote_schema(table_name)} SET SCHEMA #{quote_schema(schema)};"
end

#alter_tablespace_owner(tablespace, role) ⇒ Object

Changes a tablespace’s owner.



44
45
46
 
# File 'lib/active_record/postgresql_extensions/tablespaces.rb', line 44

def alter_tablespace_owner(tablespace, role)
  execute("ALTER TABLESPACE #{quote_tablespace(tablespace)} OWNER TO #{quote_role(role)};")
end

#alter_tablespace_parameters(tablespace, parameters_and_values) ⇒ Object 



48
49
50
51
52
53
54
55
56
57
58
59
 
# File 'lib/active_record/postgresql_extensions/tablespaces.rb', line 48

def alter_tablespace_parameters(tablespace, parameters_and_values)
  sql = "ALTER TABLESPACE #{quote_tablespace(tablespace)} SET ("

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

  sql << "\n);"

  execute(sql)
end

#alter_text_search_configuration_mapping(name, tokens, dictionaries) ⇒ Object 



35
36
37
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 35

def alter_text_search_configuration_mapping(name, tokens, dictionaries)
  add_or_alter_text_search_configuration_mapping(name, tokens, dictionaries, :action => :alter)
end

#alter_text_search_configuration_mapping_replace_dictionary(name, mappings, old_dictionary, new_dictionary) ⇒ Object 



77
78
79
80
81
82
83
84
85
86
87
88
89
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 77

def alter_text_search_configuration_mapping_replace_dictionary(name, mappings, old_dictionary, new_dictionary)
  if mappings.blank?
    raise ArgumentError.new("Expected one or more mappings to alter.")
  end

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

  execute("#{sql};")
end

#alter_text_search_configuration_owner(name, role) ⇒ Object 



120
121
122
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 120

def alter_text_search_configuration_owner(name, role)
  execute "ALTER TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} OWNER TO #{quote_role(role)};"
end

#alter_text_search_configuration_schema(name, schema) ⇒ Object 



124
125
126
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 124

def alter_text_search_configuration_schema(name, schema)
  execute "ALTER TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} SET SCHEMA #{quote_schema(schema)};"
end

#alter_text_search_dictionary(name, options) ⇒ Object 



172
173
174
175
176
177
178
179
180
181
182
183
184
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 172

def alter_text_search_dictionary(name, options)
  if options.blank?
    raise ArgumentError.new("Expected some options to alter.")
  end

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

  execute("#{sql};")
end

#alter_text_search_dictionary_owner(name, role) ⇒ Object 



193
194
195
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 193

def alter_text_search_dictionary_owner(name, role)
  execute "ALTER TEXT SEARCH DICTIONARY #{quote_generic_with_schema(name)} OWNER TO #{quote_role(role)};"
end

#alter_text_search_dictionary_schema(name, schema) ⇒ Object 



197
198
199
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 197

def alter_text_search_dictionary_schema(name, schema)
  execute "ALTER TEXT SEARCH DICTIONARY #{quote_generic_with_schema(name)} SET SCHEMA #{quote_schema(schema)};"
end

#alter_text_search_parser_schema(name, schema) ⇒ Object 



291
292
293
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 291

def alter_text_search_parser_schema(name, schema)
  execute "ALTER TEXT SEARCH PARSER #{quote_generic_with_schema(name)} SET SCHEMA #{quote_schema(schema)};"
end

#alter_text_search_template_schema(name, schema) ⇒ Object 



243
244
245
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 243

def alter_text_search_template_schema(name, schema)
  execute "ALTER TEXT SEARCH TEMPLATE #{quote_generic_with_schema(name)} SET SCHEMA #{quote_schema(schema)};"
end

#alter_view_drop_column_default(name, column, options = {}) ⇒ Object

Drop a column default from a view.



115
116
117
118
119
 
# File 'lib/active_record/postgresql_extensions/views.rb', line 115

def alter_view_drop_column_default(name, column, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :drop_default => column
  }, options).to_sql
end

#alter_view_owner(name, role, options = {}) ⇒ Object

Change the ownership of a view.



76
77
78
79
80
 
# File 'lib/active_record/postgresql_extensions/views.rb', line 76

def alter_view_owner(name, role, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :owner_to => role
  }, options).to_sql
end

#alter_view_reset_options(name, *args) ⇒ Object

Resets a view’s options.



97
98
99
100
101
102
103
 
# File 'lib/active_record/postgresql_extensions/views.rb', line 97

def alter_view_reset_options(name, *args)
  options = args.extract_options!

  execute PostgreSQLViewAlterer.new(self, name, {
    :reset_options => args
  }, options).to_sql
end

#alter_view_schema(name, schema, options = {}) ⇒ Object

Alter a view’s schema.



83
84
85
86
87
 
# File 'lib/active_record/postgresql_extensions/views.rb', line 83

def alter_view_schema(name, schema, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :set_schema => schema
  }, options).to_sql
end

#alter_view_set_column_default(name, column, expression, options = {}) ⇒ Object

Set a column default on a view.



106
107
108
109
110
111
112
 
# File 'lib/active_record/postgresql_extensions/views.rb', line 106

def alter_view_set_column_default(name, column, expression, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :set_default => {
      column => expression
    }
  }, options).to_sql
end

#alter_view_set_options(name, set_options, options = {}) ⇒ Object

Sets a view’s options using a Hash.



90
91
92
93
94
 
# File 'lib/active_record/postgresql_extensions/views.rb', line 90

def alter_view_set_options(name, set_options, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :set_options => set_options
  }, options).to_sql
end

#change_column_default_with_expression(table_name, column_name, default) ⇒ Object

:nodoc:



734
735
736
737
738
739
740
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 734

def change_column_default_with_expression(table_name, column_name, default) #:nodoc:
  if default.is_a?(Hash) && default.has_key?(:expression)
    execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{default[:expression]};"
  else
    change_column_default_without_expression(table_name, column_name, default)
  end
end

#change_column_null_with_expression(table_name, column_name, null, default = nil) ⇒ Object

:nodoc:



743
744
745
746
747
748
749
750
751
752
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 743

def change_column_null_with_expression(table_name, column_name, null, default = nil) #:nodoc:
  if default.is_a?(Hash) && default.has_key?(:expression)
    unless null
      execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)} = #{default[:expression]} WHERE #{quote_column_name(column_name)} IS NULL")
    end
    execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
  else
    change_column_null_without_expression(table_name, column_name, null, default = nil)
  end
end

#change_column_with_using(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



719
720
721
722
723
724
725
726
727
728
729
730
731
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 719

def change_column_with_using(table_name, column_name, type, options = {}) #:nodoc:
  if options.has_key?(:using) && options[:using].present?
    clear_cache!
    quoted_table_name = quote_table_name(table_name)

    execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])} USING #{options[:using]}"

    change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
    change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
  else
    change_column_without_using(table_name, column_name, type, options)
  end
end

#change_table(table_name, options = {}) {|table| ... } ⇒ Object

Yields:

  • (table) 


124
125
126
127
128
129
130
131
132
133
 
# 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(name, options = {}) ⇒ Object

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.



698
699
700
701
702
703
704
705
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 698

def cluster(name, options = {})
  sql = 'CLUSTER '
  sql << 'VERBOSE ' if options[:verbose]
  sql << quote_table_name(name)
  sql << " USING #{quote_generic(options[:using])}" if options[:using]

  execute "#{sql};"
end

#cluster_all(options = {}) ⇒ Object

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

Options

  • :verbose - Adds the VERBOSE clause.



683
684
685
686
687
688
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 683

def cluster_all(options = {})
  sql = 'CLUSTER'
  sql << ' VERBOSE' if options[:verbose]

  execute "#{sql};"
end

#cluster_materialized_view(name, index_name) ⇒ Object

Cluster a materialized view on an index.



124
125
126
127
128
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 124

def cluster_materialized_view(name, index_name)
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :cluster_on => index_name
  }).to_sql
end

#copy_from(table_name, file, options = {}) ⇒ Object Also known as: copy_from_file

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.



318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
 
# 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

#create_enum(name, *values) ⇒ Object

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



37
38
39
 
# File 'lib/active_record/postgresql_extensions/types.rb', line 37

def create_enum(name, *values)
  execute PostgreSQLEnumDefinition.new(self, name, *values).to_s
end

#create_event_trigger(name, event, function, options = {}) ⇒ Object

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



22
23
24
25
26
 
# File 'lib/active_record/postgresql_extensions/event_triggers.rb', line 22

def create_event_trigger(name, event, function, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:event_triggers)

  execute PostgreSQLEventTriggerDefinition.new(self, name, event, function, options).to_s
end

#create_extension(name, options = {}) ⇒ Object

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.



10
11
12
13
14
15
16
17
18
19
20
21
 
# File 'lib/active_record/postgresql_extensions/extensions.rb', line 10

def create_extension(name, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

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

  execute("#{sql};")
end

#create_function(name, *args) ⇒ Object

:call-seq:

create_function(name, arguments, returns, language, options = {}, &block)
create_function(name, arguments, returns, language, body, options = {})
create_function(name, returns, language, options = {}, &block)

Creates a PostgreSQL function/stored procedure.

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

returns is the return type for the function.

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

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

Options

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

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

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

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

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

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

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

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

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

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

Examples

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

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

# Both produce:
#
# CREATE OR REPLACE FUNCTION "tester_function"(integer) RETURNS integer AS $$
#   select $1;
# $$
# LANGUAGE "sql"
#   IMMUTABLE
#   SET "search_path" FROM CURRENT;

Raises:

  • (ArgumentError) 


123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/active_record/postgresql_extensions/functions.rb', line 123

def create_function(name, *args)
  options = args.extract_options!

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

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

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

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

    args.first
  elsif block_given?
    yield.to_s
  end

  execute PostgreSQLFunctionDefinition.new(self, name, arguments, returns, language, body, options).to_s
end

#create_index(name, object, columns, options = {}) ⇒ Object

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

Differences to add_index

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

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

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

Options

  • :unique - adds UNIQUE to the index definition.

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

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

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

  • :tablespace - sets the tablespace for the index.

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

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

Column Options

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

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

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

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

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

Examples

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

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

# additional options
create_index('search_idx', :foo, :tsvector, :using => :gin)
# => CREATE INDEX "search_idx" ON "foo" USING "gin"("tsvector");



113
114
115
 
# File 'lib/active_record/postgresql_extensions/indexes.rb', line 113

def create_index(name, object, columns, options = {})
  execute PostgreSQLIndexDefinition.new(self, name, object, columns, options).to_s
end

#create_language(language, options = {}) ⇒ Object

Creates a PostgreSQL procedural language.

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

Options

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

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

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

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



34
35
36
37
38
39
40
41
 
# File 'lib/active_record/postgresql_extensions/languages.rb', line 34

def create_language(language, options = {})
  sql = 'CREATE '
  sql << 'TRUSTED ' if options[:trusted]
  sql << "PROCEDURAL LANGUAGE #{quote_language(language)}"
  sql << " HANDLER #{quote_language(options[:call_handler])}" if options[:call_handler]
  sql << " VALIDATOR #{options[:validator]}" if options[:validator]
  execute("#{sql};")
end

#create_materialized_view(name, query, options = {}) ⇒ Object

Creates a new PostgreSQL materialized view.

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

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

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

Options

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

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

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

Examples

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

create_view(
  { :geospatial => :foo_view },
  'SELECT * FROM bar',
  :columns => [ :id, :name, :the_geom ],
  :with_data => false
)
# => CREATE MATERIALIZED VIEW "geospatial"."foo_view" ("id", "name", "the_geom") AS SELECT * FROM bar WITH NO DATA;



44
45
46
47
48
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 44

def create_materialized_view(name, query, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:materialized_views)

  execute PostgreSQLMaterializedViewDefinition.new(self, name, query, options).to_s
end

#create_role(name, options = {}) ⇒ Object Also known as: create_user

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



14
15
16
 
# 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

#create_rule(name, event, table, action, commands, options = {}) ⇒ Object

Creates a PostgreSQL rule.

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

action can be one of :instead or :also.

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

Options

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

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

Examples

create_rule(
  'check_it_out_rule',
  :select,
  :child,
  :instead,
  'select * from public.another', :conditions => 'id = 1'
)
# => CREATE RULE "check_it_out_rule" AS ON SELECT TO "child" WHERE id = 1 DO INSTEAD select * from public.another;



48
49
50
 
# File 'lib/active_record/postgresql_extensions/rules.rb', line 48

def create_rule(name, event, table, action, commands, options = {})
  execute PostgreSQLRuleDefinition.new(self, name, event, table, action, commands, options).to_s
end

#create_schema(schema, options = {}) ⇒ Object

Creates a new PostgreSQL schema.

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

Options

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

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



20
21
22
23
24
25
26
27
28
29
30
31
32
 
# File 'lib/active_record/postgresql_extensions/schemas.rb', line 20

def create_schema(schema, options = {})
  sql = 'CREATE SCHEMA '

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

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

  sql << quote_schema(schema)
  sql << " AUTHORIZATION #{quote_role(options[:authorization])}" if options[:authorization]
  execute("#{sql};")
end

#create_schema_authorization(name, options = {}) ⇒ Object

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

Options

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



70
71
72
73
74
75
76
77
78
79
80
81
82
 
# File 'lib/active_record/postgresql_extensions/schemas.rb', line 70

def create_schema_authorization(name, options = {})
  sql = 'CREATE SCHEMA'

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

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

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

  execute("#{sql};")
end

#create_sequence(name, options = {}) ⇒ Object

Creates a sequence.

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

Options

  • :temporary - creates a temporary sequence.

  • :incement - sets the sequence increment value.

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

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

  • :start - the initial value of the sequence.

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

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

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

Example

create_sequence(
  'what_a_sequence_of_events',
  :increment => 2,
  :cache => 2,
  :min_value => nil,
  :max_value => 10,
  :owned_by => [ :foo, :id ]
)
# => CREATE SEQUENCE "what_a_sequence_of_events" INCREMENT BY 2
#    NO MINVALUE MAXVALUE 10 CACHE 2 OWNED BY "foo"."id";



55
56
57
 
# File 'lib/active_record/postgresql_extensions/sequences.rb', line 55

def create_sequence(name, options = {})
  execute PostgreSQLSequenceDefinition.new(self, :create, name, options).to_s
end

#create_table(table_name, options = {}) {|table_definition| ... } ⇒ Object

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

PostgreSQL-specific Do-dads

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

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

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

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

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

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

  • :of_type - for “OF type_name” clauses.

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

  • :unlogged - creates an UNLOGGED table.

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

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

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

  • you can add column constraints.

  • you can add constraints to the table itself.

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

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

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

Examples

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

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

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

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

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

Yields:

  • (table_definition) 


107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
# 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

#create_tablespace(name, location, options = {}) ⇒ Object

Creates a new PostgreSQL tablespace.



14
15
16
17
18
19
20
 
# File 'lib/active_record/postgresql_extensions/tablespaces.rb', line 14

def create_tablespace(name, location, options = {})
  sql = "CREATE TABLESPACE #{quote_tablespace(name)} "
  sql << "OWNER #{quote_role(options[:owner])} " if options[:owner]
  sql << "LOCATION #{quote(location)}"

  execute("#{sql};")
end

#create_text_search_configuration(name, options = {}) ⇒ Object

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.



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 10

def create_text_search_configuration(name, options = {})
  if options[:parser_name] && options[:source_config]
    raise ArgumentError.new("You can't define both :parser_name and :source_config options.")
  elsif options[:parser_name].blank? && options[:source_config].blank?
    raise ArgumentError.new("You must provide either a :parser_name or a :source_config.")
  end

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

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

  sql << ")"
  execute("#{sql};")
end

#create_text_search_dictionary(name, template, options = {}) ⇒ Object 



143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 143

def create_text_search_dictionary(name, template, options = {})
  sql = "CREATE TEXT SEARCH DICTIONARY #{quote_generic_with_schema(name)} ("
  sql << "TEMPLATE = #{quote_generic_with_schema(template)}"

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

  sql << ')'

  execute("#{sql};")
end

#create_text_search_parser(name, options = {}) ⇒ Object

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



251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 251

def create_text_search_parser(name, options = {})
  if (missing_options = [ :start, :gettoken, :end, :lextypes ] - options.keys).present?
    raise ArgumentError.new("Missing options: #{missing_options}.")
  end

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

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

  sql << ')'

  execute("#{sql};")
end

#create_text_search_template(name, options = {}) ⇒ Object

Options

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



206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 206

def create_text_search_template(name, options = {})
  if options[:lexize].blank?
    raise ArgumentError.new("Expected to see a :lexize option.")
  end

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

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

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

  execute("#{sql};")
end

#create_trigger(name, called, events, table, function, options = {}) ⇒ Object

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"();



85
86
87
 
# File 'lib/active_record/postgresql_extensions/triggers.rb', line 85

def create_trigger(name, called, events, table, function, options = {})
  execute PostgreSQLTriggerDefinition.new(self, name, called, events, table, function, options).to_s
end

#create_view(name, query, options = {}) ⇒ Object

Creates a new PostgreSQL view.

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

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

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

Options

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

  • :temporary - adds a TEMPORARY clause.

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

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

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

Examples

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

create_view(
  { :geospatial => :foo_view },
  'SELECT * FROM bar',
  :columns => [ :id, :name, :the_geom ]
)
# => CREATE VIEW "geospatial"."foo_view" ("id", "name", "the_geom") AS SELECT * FROM bar;



47
48
49
 
# File 'lib/active_record/postgresql_extensions/views.rb', line 47

def create_view(name, query, options = {})
  execute PostgreSQLViewDefinition.new(self, name, query, options).to_s
end

#current_role ⇒ Object Also known as: current_user 



445
446
447
448
449
450
451
 
# 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

#current_scoped_schema ⇒ Object

Get the current scoped schema.



82
83
84
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 82

def current_scoped_schema
  scoped_schemas.last
end

#disable_event_trigger(name) ⇒ Object

Disables an event trigger.



75
76
77
 
# 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_referential_integrity_with_views ⇒ Object

:nodoc:



484
485
486
487
488
489
490
491
492
493
494
495
496
497
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 484

def disable_referential_integrity_with_views #:nodoc:
  if supports_disable_referential_integrity? then
    execute(tables_without_views.collect { |name|
      "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL"
    }.join(";"))
  end
  yield
ensure
  if supports_disable_referential_integrity? then
    execute(tables_without_views.collect { |name|
      "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL"
    }.join(";"))
  end
end

#disable_triggers(table, *triggers) ⇒ Object

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.



526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 526

def disable_triggers(table, *triggers)
  quoted_table_name = quote_table_name(table)
  triggers = if triggers.present?
    triggers.collect { |trigger|
      case trigger
        when :all, :user
          trigger.to_s.upcase
        else
          quote_generic(trigger)
      end
    }
  else
    'ALL'
  end

  Array.wrap(triggers).each do |trigger|
    execute("ALTER TABLE #{quoted_table_name} DISABLE TRIGGER #{trigger};")
  end
end

#drop_constraint(table, name, options = {}) ⇒ Object

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.



70
71
72
73
74
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 70

def drop_constraint(table, name, options = {})
  sql = "ALTER TABLE #{quote_table_name(table)} DROP CONSTRAINT #{quote_generic(name)}"
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end

#drop_event_trigger(name, options = {}) ⇒ Object

Drops an event trigger.

Options

  • :if_exists - adds IF EXISTS.

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



35
36
37
38
39
40
41
 
# File 'lib/active_record/postgresql_extensions/event_triggers.rb', line 35

def drop_event_trigger(name, options = {})
  sql = 'DROP EVENT TRIGGER '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic(name)
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end

#drop_extension(*args) ⇒ Object

Options

  • if_exists - adds IF EXISTS.

  • cascade - adds CASCADE.



27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/active_record/postgresql_extensions/extensions.rb', line 27

def drop_extension(*args)
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

  options = args.extract_options!

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

  execute("#{sql};")
end

#drop_function(name, *args) ⇒ Object

Drops a function.

Options

  • :if_exists - adds an IF EXISTS clause.

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

Raises:

  • (ArgumentError) 


161
162
163
164
165
166
167
168
169
170
171
172
 
# File 'lib/active_record/postgresql_extensions/functions.rb', line 161

def drop_function(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(1, 2)

  options = args.extract_options!
  arguments = args.first

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

#drop_index(*args) ⇒ Object

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.



137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/active_record/postgresql_extensions/indexes.rb', line 137

def drop_index(*args)
  options = args.extract_options!
  args.flatten!

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

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

#drop_language(language, options = {}) ⇒ Object

Drops a language.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.



49
50
51
52
53
54
55
 
# File 'lib/active_record/postgresql_extensions/languages.rb', line 49

def drop_language(language, options = {})
  sql = 'DROP PROCEDURAL LANGUAGE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_language(language)
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end

#drop_materialized_view(*args) ⇒ Object

Drops a materialized view.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.



56
57
58
59
60
61
62
63
64
65
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 56

def drop_materialized_view(*args)
  options = args.extract_options!
  args.flatten!

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

#drop_role(*args) ⇒ Object Also known as: drop_user

Drop PostgreSQL ROLEs.

Options

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



31
32
33
34
35
36
37
38
39
 
# 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

#drop_rule(name, table) ⇒ Object

Drops a PostgreSQL rule.



53
54
55
 
# File 'lib/active_record/postgresql_extensions/rules.rb', line 53

def drop_rule(name, table)
  execute "DROP RULE #{quote_rule(name)} ON #{quote_table_name(table)};"
end

#drop_schema(*args) ⇒ Object

Drops a schema.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.



40
41
42
43
44
45
46
47
48
49
 
# File 'lib/active_record/postgresql_extensions/schemas.rb', line 40

def drop_schema(*args)
  options = args.extract_options!
  args.flatten!

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

#drop_sequence(*args) ⇒ Object

Drops a sequence.

Options

  • :if_exists - adds IF EXISTS.

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



66
67
68
69
70
71
72
73
74
75
 
# File 'lib/active_record/postgresql_extensions/sequences.rb', line 66

def drop_sequence(*args)
  options = args.extract_options!
  args.flatten!

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

#drop_table(*args) ⇒ Object

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.



148
149
150
151
152
153
154
155
156
157
 
# 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

#drop_tablespace(name, options = {}) ⇒ Object

Drops a tablespace.

Options

  • :if_exists - adds IF EXISTS.



27
28
29
30
31
32
33
 
# File 'lib/active_record/postgresql_extensions/tablespaces.rb', line 27

def drop_tablespace(name, options = {})
  sql = 'DROP TABLESPACE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_tablespace(name)

  execute("#{sql};")
end

#drop_text_search_configuration(name, options = {}) ⇒ Object

Drops a text search configuration.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.



134
135
136
137
138
139
140
141
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 134

def drop_text_search_configuration(name, options = {})
  sql = 'DROP TEXT SEARCH CONFIGURATION '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic_with_schema(name)
  sql << ' CASCADE' if options[:cascade]

  execute("#{sql};")
end

#drop_text_search_configuration_mapping(name, *args) ⇒ Object 



91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 91

def drop_text_search_configuration_mapping(name, *args)
  options = args.extract_options!
  mappings = args

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

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

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

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

  execute("#{sql};")
end

#drop_text_search_dictionary(name, options = {}) ⇒ Object

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.



163
164
165
166
167
168
169
170
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 163

def drop_text_search_dictionary(name, options = {})
  sql = 'DROP TEXT SEARCH DICTIONARY '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic_with_schema(name)
  sql << ' CASCADE' if options[:cascade]

  execute("#{sql};")
end

#drop_text_search_parser(name, options = {}) ⇒ Object

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.



275
276
277
278
279
280
281
282
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 275

def drop_text_search_parser(name, options = {})
  sql = 'DROP TEXT SEARCH PARSER '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic_with_schema(name)
  sql << ' CASCADE' if options[:cascade]

  execute("#{sql};")
end

#drop_text_search_template(name, options = {}) ⇒ Object

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.



227
228
229
230
231
232
233
234
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 227

def drop_text_search_template(name, options = {})
  sql = 'DROP TEXT SEARCH TEMPLATE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << quote_generic_with_schema(name)
  sql << ' CASCADE' if options[:cascade]

  execute("#{sql};")
end

#drop_trigger(name, table, options = {}) ⇒ Object

Drops a trigger.

Options

  • :if_exists - adds IF EXISTS.

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



96
97
98
99
100
101
102
 
# File 'lib/active_record/postgresql_extensions/triggers.rb', line 96

def drop_trigger(name, table, options = {})
  sql = 'DROP TRIGGER '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << "#{quote_generic(name)} ON #{quote_table_name(table)}"
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end

#drop_type(*args) ⇒ Object

Drop TYPEs.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.



77
78
79
80
81
82
83
84
85
86
 
# File 'lib/active_record/postgresql_extensions/types.rb', line 77

def drop_type(*args)
  options = args.extract_options!
  args.flatten!

  sql = 'DROP TYPE '
  sql << 'IF EXISTS ' if options[:if_exists]
  sql << Array.wrap(args).collect { |i| quote_generic(i) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end

#drop_view(*args) ⇒ Object

Drops a view.

Options

  • :if_exists - adds IF EXISTS.

  • :cascade - adds CASCADE.



57
58
59
60
61
62
63
64
65
66
 
# File 'lib/active_record/postgresql_extensions/views.rb', line 57

def drop_view(*args)
  options = args.extract_options!
  args.flatten!

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

#enable_event_trigger(name, options = {}) ⇒ Object

Enables an event trigger.

Options

  • <tt>:replica



58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/active_record/postgresql_extensions/event_triggers.rb', line 58

def enable_event_trigger(name, options = {})
  if options[:always] && options[:replica]
    raise ArgumentError.new("Cannot use :replica and :always together when enabling an event trigger.")
  end

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

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

  execute "#{sql};"
end

#enable_triggers(table, *triggers) ⇒ Object

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



502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 502

def enable_triggers(table, *triggers)
  quoted_table_name = quote_table_name(table)
  triggers = if triggers.present?
    triggers.collect { |trigger|
      case trigger
        when :all, :user
          trigger.to_s.upcase
        else
          quote_generic(trigger)
      end
    }
  else
    'ALL'
  end

  Array.wrap(triggers).each do |trigger|
    execute("ALTER TABLE #{quoted_table_name} ENABLE TRIGGER #{trigger};")
  end
end

#enum_values(name) ⇒ Object

Returns an Array of possible



89
90
91
 
# File 'lib/active_record/postgresql_extensions/types.rb', line 89

def enum_values(name)
  query(%{SELECT unnest(enum_range(NULL::#{quote_generic(name)}))}, 'Enum values').map(&:first)
end

#extract_schema_and_table_names(name) ⇒ Object 



215
216
217
218
219
220
221
222
223
224
225
226
227
228
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 215

def extract_schema_and_table_names(name)
  if name.is_a?(Hash)
    [ name.keys.first.to_s, name.values.first.to_s ]
  else
    schema, name_part = extract_pg_identifier_from_name(name.to_s)

    unless name_part
      [ nil, schema.to_s ]
    else
      table_name, name_part = extract_pg_identifier_from_name(name_part)
      [ schema.to_s, table_name.to_s ]
    end
  end
end

#extract_schema_name(name) ⇒ Object 



205
206
207
208
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 205

def extract_schema_name(name)
  schema, _ = extract_schema_and_table_names(name)
  schema if schema
end

#extract_table_name(name) ⇒ Object 



210
211
212
213
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 210

def extract_table_name(name)
  _, name_part = extract_schema_and_table_names(name)
  name_part if name_part
end

#foreign_keys(table_name, name = nil) ⇒ Object

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.



562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 562

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

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

#grant_database_privileges(databases, privileges, roles, options = {}) ⇒ Object

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.



33
34
35
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 33

def grant_database_privileges(databases, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :database, databases, privileges, roles, options).to_sql
end

#grant_function_privileges(function_prototypes, privileges, roles, options = {}) ⇒ Object

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.



41
42
43
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 41

def grant_function_privileges(function_prototypes, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :function, function_prototypes, privileges, roles, options, :quote_objects => false).to_sql
end

#grant_language_privileges(languages, privileges, roles, options = {}) ⇒ Object

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.



49
50
51
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 49

def grant_language_privileges(languages, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :language, languages, privileges, roles, options).to_sql
end

#grant_materialized_view_privileges(materialized_views, privileges, roles, options = {}) ⇒ Object

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.



81
82
83
84
85
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 81

def grant_materialized_view_privileges(materialized_views, privileges, roles, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:materialized_views)

  execute PostgreSQLGrantPrivilege.new(self, :materialized_view, materialized_views, privileges, roles, options, :named_object_type => false).to_sql
end

#grant_role_membership(roles, role_names, options = {}) ⇒ Object

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.



95
96
97
98
99
100
101
102
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 95

def grant_role_membership(roles, role_names, options = {})
  sql = "GRANT "
  sql << Array.wrap(roles).collect { |r| quote_role(r) }.join(', ')
  sql << ' TO '
  sql << Array.wrap(role_names).collect { |r| quote_role(r) }.join(', ')
  sql << ' WITH ADMIN OPTION' if options[:with_admin_option]
  execute("#{sql};")
end

#grant_schema_privileges(schemas, privileges, roles, options = {}) ⇒ Object

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.



57
58
59
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 57

def grant_schema_privileges(schemas, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :schema, schemas, privileges, roles, options, :ignore_schema => true).to_sql
end

#grant_sequence_privileges(sequences, privileges, roles, options = {}) ⇒ Object

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.



25
26
27
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 25

def grant_sequence_privileges(sequences, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :sequence, sequences, privileges, roles, options).to_sql
end

#grant_table_privileges(tables, privileges, roles, options = {}) ⇒ Object

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.



17
18
19
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 17

def grant_table_privileges(tables, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :table, tables, privileges, roles, options).to_sql
end

#grant_tablespace_privileges(tablespaces, privileges, roles, options = {}) ⇒ Object

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.



65
66
67
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 65

def grant_tablespace_privileges(tablespaces, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :tablespace, tablespaces, privileges, roles, options).to_sql
end

#grant_view_privileges(views, privileges, roles, options = {}) ⇒ Object

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.



73
74
75
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 73

def grant_view_privileges(views, privileges, roles, options = {})
  execute PostgreSQLGrantPrivilege.new(self, :view, views, privileges, roles, options, :named_object_type => false).to_sql
end

#ignore_scoped_schema ⇒ Object

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")



68
69
70
71
72
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 68

def ignore_scoped_schema
  with_schema nil do
    yield
  end
end

#ignored_tables(name = nil) ⇒ Object

Returns an Array of tables to ignore.



455
456
457
458
459
460
461
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 455

def ignored_tables(name = nil)
  query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map { |row| row[0] }
    SELECT tablename
    FROM pg_tables
    WHERE schemaname IN ('pg_catalog');
  SQL
end

#language_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


75
76
77
 
# File 'lib/active_record/postgresql_extensions/languages.rb', line 75

def language_exists?(name)
  languages.include?(name.to_s)
end

#languages(name = nil) ⇒ Object

Returns an Array of available languages.



68
69
70
71
72
73
 
# 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

#native_database_types_with_spatial_types ⇒ Object

:nodoc:



23
24
25
26
27
28
 
# File 'lib/active_record/postgresql_extensions/geometry.rb', line 23

def native_database_types_with_spatial_types #:nodoc:
  native_database_types_without_spatial_types.merge({
    :geometry => { :name => 'geometry' },
    :geography => { :name => 'geography' }
  })
end

#original_change_table ⇒ Object 



123
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 123

alias :original_change_table :change_table

#original_create_table ⇒ Object 



21
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 21

alias :original_create_table :create_table

#original_drop_table ⇒ Object 



135
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 135

alias :original_drop_table :drop_table

#original_rename_table ⇒ Object 



159
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 159

alias :original_rename_table :rename_table

#quote_function(name) ⇒ Object

Quoting method for server-side functions.



153
154
155
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 153

def quote_function(name)
  quote_generic_with_schema(name)
end

#quote_generic(g) ⇒ Object 



88
89
90
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 88

def quote_generic(g)
  quote_column_name(g)
end

#quote_generic_ignore_scoped_schema(g) ⇒ Object

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



99
100
101
102
103
104
105
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 99

def quote_generic_ignore_scoped_schema(g)
  if g.is_a?(Hash)
    quote_generic g.values.first
  else
    quote_generic g
  end
end

#quote_generic_with_schema(g) ⇒ Object

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



109
110
111
112
113
114
115
116
117
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 109

def quote_generic_with_schema(g)
  if g.is_a?(Hash)
    "#{quote_schema(g.keys.first)}.#{quote_generic(g.values.first)}"
  else
    if current_scoped_schema
      quote_schema(current_scoped_schema) << '.'
    end.to_s << quote_generic(g)
  end
end

#quote_language(language) ⇒ Object

Quoting method for procedural languages.



130
131
132
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 130

def quote_language(language)
  quote_generic(language)
end

#quote_role(role) ⇒ Object

Quoting method for roles.



120
121
122
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 120

def quote_role(role)
  quote_generic(role)
end

#quote_rule(rule) ⇒ Object

Quoting method for rules.



125
126
127
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 125

def quote_rule(rule)
  quote_generic(rule)
end

#quote_schema(schema) ⇒ Object

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



137
138
139
140
141
142
143
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 137

def quote_schema(schema)
  if schema.to_s.upcase == 'PUBLIC'
    'PUBLIC'
  else
    quote_generic(schema)
  end
end

#quote_sequence(name) ⇒ Object

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



148
149
150
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 148

def quote_sequence(name)
  quote_generic_with_schema(name)
end

#quote_table_name_with_schemas(name) ⇒ Object

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"



184
185
186
187
188
189
190
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 184

def quote_table_name_with_schemas(name)
  if current_scoped_schema || name.is_a?(Hash)
    quote_generic_with_schema(name)
  else
    quote_table_name_without_schemas(name)
  end
end

#quote_tablespace(name) ⇒ Object

Quoting method for tablespaces.



201
202
203
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 201

def quote_tablespace(name)
  quote_generic(name)
end

#quote_view_name(name) ⇒ Object

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



196
197
198
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 196

def quote_view_name(name)
  quote_table_name(name)
end

#referenced_foreign_keys(table_name, name = nil) ⇒ Object

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.



620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 620

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

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

#refresh_materialized_view(name, options = {}) ⇒ Object

Refreshes the data in a materialized view.

Options

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



143
144
145
146
147
148
149
150
151
152
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 143

def refresh_materialized_view(name, options = {})
  options = {
    :with_data => true
  }.merge(options)

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

  execute "#{sql};"
end

#remove_cluster_from_materialized_view(name) ⇒ Object

Remove a cluster from materialized view.



131
132
133
134
135
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 131

def remove_cluster_from_materialized_view(name)
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :remove_cluster => true
  }).to_sql
end

#rename_event_trigger(name, new_name) ⇒ Object

Renames an event trigger.



44
45
46
 
# File 'lib/active_record/postgresql_extensions/event_triggers.rb', line 44

def rename_event_trigger(name, new_name)
  execute "ALTER EVENT TRIGGER #{quote_generic(name)} RENAME TO #{quote_generic(new_name)};"
end

#rename_function(name, *args) ⇒ Object

Renames a function.

Raises:

  • (ArgumentError) 


175
176
177
178
179
180
181
182
183
 
# File 'lib/active_record/postgresql_extensions/functions.rb', line 175

def rename_function(name, *args)
  raise ArgumentError.new("Expected 2-3 arguments") unless args.length.between?(1, 2)

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

  execute PostgreSQLFunctionAlterer.new(self, name, arguments, :rename_to => rename_to).to_s
end

#rename_index(name, new_name, options = {}) ⇒ Object

Renames an index.



156
157
158
 
# File 'lib/active_record/postgresql_extensions/indexes.rb', line 156

def rename_index(name, new_name, options = {})
  execute "ALTER INDEX #{quote_generic(name)} RENAME TO #{quote_generic(new_name)};"
end

#rename_materialized_view(name, new_name, options = {}) ⇒ Object

Renames a materialized view.



68
69
70
71
72
 
# File 'lib/active_record/postgresql_extensions/materialized_views.rb', line 68

def rename_materialized_view(name, new_name, options = {})
  execute PostgreSQLMaterializedViewAlterer.new(self, name, {
    :rename_to => new_name
  }, options).to_sql
end

#rename_rule(old_name, table, new_name) ⇒ Object

Renames a rule. Available in PostgreSQL 9.3+.



58
59
60
 
# File 'lib/active_record/postgresql_extensions/rules.rb', line 58

def rename_rule(old_name, table, new_name)
  execute "ALTER RULE #{quote_rule(old_name)} ON #{quote_table_name(table)} RENAME TO #{quote_rule(new_name)};"
end

#rename_sequence(name, rename, options = {}) ⇒ Object

Renames the sequence.



78
79
80
 
# File 'lib/active_record/postgresql_extensions/sequences.rb', line 78

def rename_sequence(name, rename, options = {})
  execute("ALTER SEQUENCE #{quote_sequence(name)} RENAME TO #{quote_generic_ignore_scoped_schema(rename)};")
end

#rename_table(name, new_name, options = {}) ⇒ Object

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.



164
165
166
 
# 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

#rename_tablespace(old_name, new_name) ⇒ Object

Renames a tablespace.



39
40
41
 
# File 'lib/active_record/postgresql_extensions/tablespaces.rb', line 39

def rename_tablespace(old_name, new_name)
  execute("ALTER TABLESPACE #{quote_tablespace(old_name)} RENAME TO #{quote_tablespace(new_name)};")
end

#rename_text_search_configuration(old_name, new_name) ⇒ Object 



113
114
115
116
117
118
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 113

def rename_text_search_configuration(old_name, new_name)
  execute("ALTER TEXT SEARCH CONFIGURATION %s RENAME TO %s;" % [
    quote_generic_with_schema(old_name),
    quote_generic_with_schema(new_name)
  ])
end

#rename_text_search_dictionary(old_name, new_name) ⇒ Object 



186
187
188
189
190
191
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 186

def rename_text_search_dictionary(old_name, new_name)
  execute("ALTER TEXT SEARCH DICTIONARY %s RENAME TO %s;" % [
    quote_generic_with_schema(old_name),
    quote_generic_with_schema(new_name)
  ])
end

#rename_text_search_parser(old_name, new_name) ⇒ Object 



284
285
286
287
288
289
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 284

def rename_text_search_parser(old_name, new_name)
  execute("ALTER TEXT SEARCH PARSER %s RENAME TO %s;" % [
    quote_generic_with_schema(old_name),
    quote_generic_with_schema(new_name)
  ])
end

#rename_text_search_template(old_name, new_name) ⇒ Object 



236
237
238
239
240
241
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 236

def rename_text_search_template(old_name, new_name)
  execute("ALTER TEXT SEARCH TEMPLATE %s RENAME TO %s;" % [
    quote_generic_with_schema(old_name),
    quote_generic_with_schema(new_name)
  ])
end

#rename_trigger(name, table, new_name, options = {}) ⇒ Object

Renames a trigger.



105
106
107
 
# File 'lib/active_record/postgresql_extensions/triggers.rb', line 105

def rename_trigger(name, table, new_name, options = {})
  execute "ALTER TRIGGER #{quote_generic(name)} ON #{quote_table_name(table)} RENAME TO #{quote_generic(new_name)};"
end

#rename_view(name, new_name, options = {}) ⇒ Object

Renames a view.



69
70
71
72
73
 
# File 'lib/active_record/postgresql_extensions/views.rb', line 69

def rename_view(name, new_name, options = {})
  execute PostgreSQLViewAlterer.new(self, name, {
    :rename_to => new_name
  }, options).to_sql
end

#replace_text_search_configuration_dictionary(name, old_dictionary, new_dictionary) ⇒ Object 



70
71
72
73
74
75
 
# File 'lib/active_record/postgresql_extensions/text_search.rb', line 70

def replace_text_search_configuration_dictionary(name, old_dictionary, new_dictionary)
  sql = "ALTER TEXT SEARCH CONFIGURATION #{quote_generic_with_schema(name)} ALTER MAPPING REPLACE "
  sql << "#{quote_generic(old_dictionary)} WITH #{quote_generic(new_dictionary)}"

  execute("#{sql};")
end

#reset_role ⇒ Object 



441
442
443
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 441

def reset_role
  execute('RESET ROLE;')
end

#reset_tablespace_parameters(tablespace, *parameters) ⇒ Object 



61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/active_record/postgresql_extensions/tablespaces.rb', line 61

def reset_tablespace_parameters(tablespace, *parameters)
  sql = "ALTER TABLESPACE #{quote_tablespace(tablespace)} RESET ("

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

  sql << "\n);"

  execute(sql)
end

#revoke_database_privileges(databases, privileges, roles, options = {}) ⇒ Object

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.



124
125
126
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 124

def revoke_database_privileges(databases, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :database, databases, privileges, roles, options).to_sql
end

#revoke_function_privileges(function_prototypes, privileges, roles, options = {}) ⇒ Object

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.



132
133
134
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 132

def revoke_function_privileges(function_prototypes, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :function, function_prototypes, privileges, roles, options, :quote_objects => false).to_sql
end

#revoke_language_privileges(languages, privileges, roles, options = {}) ⇒ Object

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.



140
141
142
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 140

def revoke_language_privileges(languages, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :language, languages, privileges, roles, options).to_sql
end

#revoke_materialized_view_privileges(materialized_views, privileges, roles, options = {}) ⇒ Object

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.



164
165
166
167
168
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 164

def revoke_materialized_view_privileges(materialized_views, privileges, roles, options = {})
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:materialized_views)

  execute PostgreSQLRevokePrivilege.new(self, :materialized_view, materialized_views, privileges, roles, options, :named_object_type => false).to_sql
end

#revoke_role_membership(roles, role_names, options = {}) ⇒ Object

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.



187
188
189
190
191
192
193
194
195
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 187

def revoke_role_membership(roles, role_names, options = {})
  sql = 'REVOKE '
  sql << 'ADMIN_OPTION_FOR ' if options[:admin_option_for]
  sql << Array.wrap(roles).collect { |r| quote_role(r) }.join(', ')
  sql << ' FROM '
  sql << Array.wrap(role_names).collect { |r| quote_role(r) }.join(', ')
  sql << ' CASCADE' if options[:cascade]
  execute("#{sql};")
end

#revoke_schema_privileges(schemas, privileges, roles, options = {}) ⇒ Object

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.



148
149
150
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 148

def revoke_schema_privileges(schemas, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :schema, schemas, privileges, roles, options, :ignore_schema => true).to_sql
end

#revoke_sequence_privileges(sequences, privileges, roles, options = {}) ⇒ Object

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.



116
117
118
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 116

def revoke_sequence_privileges(sequences, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :sequence, sequences, privileges, roles, options).to_sql
end

#revoke_table_privileges(tables, privileges, roles, options = {}) ⇒ Object

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.



108
109
110
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 108

def revoke_table_privileges(tables, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :table, tables, privileges, roles, options).to_sql
end

#revoke_tablespace_privileges(tablespaces, privileges, roles, options = {}) ⇒ Object

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.



156
157
158
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 156

def revoke_tablespace_privileges(tablespaces, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :tablespace, tablespaces, privileges, roles, options).to_sql
end

#revoke_view_privileges(views, privileges, roles, options = {}) ⇒ Object

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.



174
175
176
 
# File 'lib/active_record/postgresql_extensions/permissions.rb', line 174

def revoke_view_privileges(views, privileges, roles, options = {})
  execute PostgreSQLRevokePrivilege.new(self, :view, views, privileges, roles, options, :named_object_type => false).to_sql
end

#role_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


420
421
422
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 420

def role_exists?(name)
  roles.include?(name)
end

#roles(name = nil) ⇒ Object 



413
414
415
416
417
418
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 413

def roles(name = nil)
  query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map { |row| row[0] }
    SELECT rolname
    FROM pg_roles
  SQL
end

#schema_search_path_with_csv_fix ⇒ Object

Fix ActiveRecord bug when grabbing the current search_path.



478
479
480
 
# 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

#schema_search_path_with_csv_fix=(schema_csv) ⇒ Object

There seems to be a bug in ActiveRecord where it isn’t setting the schema search path properly because it’s using ‘,’ as a separator rather than /,s+/.



472
473
474
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 472

def schema_search_path_with_csv_fix=(schema_csv) #:nodoc:
  self.schema_search_path_without_csv_fix = schema_csv.gsub(/,\s+/, ',') if schema_csv
end

#scoped_schemas ⇒ Object

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



76
77
78
79
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 76

def scoped_schemas
  scoped_schemas = (Thread.current[:scoped_schemas] ||= {})
  scoped_schemas[self] ||= []
end

#sequence_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


129
130
131
 
# File 'lib/active_record/postgresql_extensions/sequences.rb', line 129

def sequence_exists?(name)
  sequences.include?(name.to_s)
end

#sequences(name = nil) ⇒ Object

Returns an Array of available sequences.



121
122
123
124
125
126
127
 
# File 'lib/active_record/postgresql_extensions/sequences.rb', line 121

def sequences(name = nil)
  query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map { |row| row[0] }
    SELECT c.relname AS sequencename
    FROM pg_class c
    WHERE c.relkind = 'S'::"char";
  SQL
end

#set_role(role, options = {}) ⇒ Object

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



426
427
428
429
430
431
432
433
434
435
436
437
438
439
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 426

def set_role(role, options = {})
  duration = if options[:duration]
    if [ :session, :local ].include?(options[:duration])
      options[:duration].to_s.upcase
    else
      raise ArgumentError.new("The :duration option must be one of :session or :local.")
    end
  end

  sql = 'SET '
  sql << "#{duration} " if duration
  sql << "ROLE #{quote_role(role)};"
  execute(sql, "Setting current role")
end

#set_sequence_value(name, value, options = {}) ⇒ Object

Calls the setval function on the sequence.

Options

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



106
107
108
109
110
111
112
113
114
115
116
117
118
 
# 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

#tables_with_views(name = nil) ⇒ Object

:nodoc:



463
464
465
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 463

def tables_with_views(name = nil) #:nodoc:
  tables_without_views(name) + views(name)
end

#type_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


31
32
33
 
# File 'lib/active_record/postgresql_extensions/types.rb', line 31

def type_exists?(name)
  types.include?(name.to_s)
end

#types(name = nil) ⇒ Object

Returns an Array of available languages.



11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/active_record/postgresql_extensions/types.rb', line 11

def types(name = nil)
  query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map(&:first)
    SELECT t.typname as type
      FROM pg_type t
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
      WHERE (t.typrelid = 0 OR (
        SELECT c.relkind = 'c'
          FROM pg_catalog.pg_class c
          WHERE c.oid = t.typrelid
      )) AND
        NOT EXISTS(
          SELECT 1
            FROM pg_catalog.pg_type el
            WHERE el.oid = t.typelem
              AND el.typarray = t.oid
        ) AND
        n.nspname NOT IN ('information_schema');
  SQL
end

#update_extension(name, new_version = nil) ⇒ Object 



40
41
42
43
44
45
46
 
# File 'lib/active_record/postgresql_extensions/extensions.rb', line 40

def update_extension(name, new_version = nil)
  ActiveRecord::PostgreSQLExtensions::Features.check_feature(:extensions)

  sql = "ALTER EXTENSION #{quote_generic(name)} UPDATE"
  sql << " TO #{quote_generic(new_version)}" if new_version;
  execute("#{sql};")
end

#update_geometry_srid(table_name, column_name, srid) ⇒ Object

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



32
33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/active_record/postgresql_extensions/geometry.rb', line 32

def update_geometry_srid(table_name, column_name, srid)
  schema, table = extract_schema_and_table_names(table_name)

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

  args.unshift(quote(schema)) if schema

  execute(%{SELECT UpdateGeometrySRID(#{args.join(', ')});})
end

#vacuum(*args) ⇒ Object

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



9
10
11
12
 
# File 'lib/active_record/postgresql_extensions/vacuum.rb', line 9

def vacuum(*args)
  vacuumer = PostgreSQLVacuum.new(self, *args)
  execute("#{vacuumer};")
end

#validate_constraint(table, name) ⇒ Object

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



78
79
80
 
# File 'lib/active_record/postgresql_extensions/constraints.rb', line 78

def validate_constraint(table, name)
  execute("ALTER TABLE #{quote_table_name(table)} VALIDATE CONSTRAINT #{quote_generic(name)};")
end

#view_exists?(name) ⇒ Boolean

Returns:

  • (Boolean) 


391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 391

def view_exists?(name)
  name         = name.to_s
  schema, view = name.split('.', 2)

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

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

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

#views(name = nil) ⇒ Object

Returns an Array of database views.



383
384
385
386
387
388
389
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 383

def views(name = nil)
  query(PostgreSQLExtensions::Utils.strip_heredoc(<<-SQL), name).map { |row| row[0] }
    SELECT viewname
    FROM pg_views
    WHERE schemaname = ANY (current_schemas(false))
  SQL
end

#with_schema(schema) ⇒ Object

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



24
25
26
27
28
29
30
31
 
# File 'lib/active_record/postgresql_extensions/adapter_extensions.rb', line 24

def with_schema(schema)
  scoped_schemas << schema
  begin
    yield
  ensure
    scoped_schemas.pop
  end
end

#without_triggers(table, *triggers) ⇒ Object

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.



551
552
553
554
555
556
 
# 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