SchemaPlus::Functions

SchemaPlus::Functions adds support for SQL functions in ActiveRecord.

SchemaPlus::Functions is part of the SchemaPlus family of Ruby on Rails ActiveRecord extension gems.

Installation

As usual:

gem "schema_plus_functions"                # in a Gemfile
gem.add_dependency "schema_plus_functions" # in a .gemspec

Usage

Migrations

To declare a function use create_function:

create_function :test, "start date, stop date DEFAULT NULL::date", <<-END
RETURNS integer
  LANGUAGE plpgsql
  AS $$
DECLARE
  processed INTEGER = 0;
BEGIN
  processed = processed + 1;  
  RETURN processed;
END;
$$
END

To create an aggregate function specify the function_type:

create_function :array_cat_agg, "anyarray", <<-END, function_type: :aggregate
(SFUNC=array_cat,STYPE=anyarray)
END

To update a function in a new migration specify the allow_replace property

create_function :test, "start date, stop date DEFAULT NULL::date", <<-END, allow_replace: true
RETURNS integer
  LANGUAGE plpgsql
  AS $$
DECLARE
  processed INTEGER = 0;
BEGIN
  processed = processed + 5;  
  RETURN processed;
END;
$$
END

To remove a function use drop_function with the name and argument types:

drop_function :test, "start date, stop date"

To remove an aggregate use drop_function with the name and argument types with the function_type:

drop_function :array_cat_agg, "anyarray", function_type: :aggrgate

Introspection

You can query the list of user functions at the connection level (uncached):

connection.functions

This will return a array of arrays. The inner array containing the function_name, argument types and possible function type.

Compatibility

SchemaPlus::Functions is tested on:

• ruby 2.5 with activerecord 5.2, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.5 with activerecord 6.0, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.5 with activerecord 6.1, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.7 with activerecord 5.2, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.7 with activerecord 6.0, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.7 with activerecord 6.1, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.7 with activerecord 7.0, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.0 with activerecord 6.0, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.0 with activerecord 6.1, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.0 with activerecord 7.0, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.1 with activerecord 6.0, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.1 with activerecord 6.1, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.1 with activerecord 7.0, using postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12

History

• 1.0.1 - Add AR 6.1 and 7.0 as well as Ruby 3.1
• 1.0.0 - Drop AR < 5.2, add <= 6.0 and Ruby 3.0
• 0.2.0 - Add suppot for PostgreSQL 11+
• 0.1.0 - Initial release

Development & Testing

Are you interested in contributing to SchemaPlus::Functions? Thanks! Please follow the standard protocol: fork, feature branch, develop, push, and issue pull request.

Some things to know about to help you develop and test:

• schema_dev: SchemaPlus::Functions uses schema_dev to facilitate running rspec tests on the matrix of ruby, activerecord, and database versions that the gem supports, both locally and on github actions

  To to run rspec locally on the full matrix, do:

    $ schema_dev bundle install
    $ schema_dev rspec
  

  You can also run on just one configuration at a time; For info, see schema_dev --help or the schema_dev README.

  The matrix of configurations is specified in schema_dev.yml in the project root.

• schema_plus_core: SchemaPlus::Functions uses the SchemaPlus::Core API that provides middleware callback stacks to make it easy to extend ActiveRecord's behavior. If that API is missing something you need for your contribution, please head over to schema_plus_core and open an issue or pull request.

• schema_monkey: SchemaPlus::Functions is implemented as a schema_monkey client, using schema_monkey's convention-based protocols for extending ActiveRecord and using middleware stacks.