Method: ActiveRecord::ConnectionAdapters::PostgreSQLAdapter#create_function

Defined in:
lib/active_record/postgresql_extensions/functions.rb

#create_function(name, arguments, returns, language, *args) ⇒ Object

Creates a PostgreSQL function/stored procedure.

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

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.

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;



115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/active_record/postgresql_extensions/functions.rb', line 115

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

  body = if 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