Use Postgres Function as ActiveRecord Model

Question

I have created a Postgres function that I am using in order to perform a complex query which joins many tables that all have to be filtered by a dynamic date field.

The function works perfectly, and allows my to perform a query like "SELECT * FROM trail_for_date('2014-01-01')" and returns a table.

A simplified example from the Postgres documentation on functions:

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

How could I use this return table as a Rails/Ruby model where the argument to the function is dynamic?

Something like the following (which obviously doesn't work):

class SimplifiedExample < ActiveRecord::Base
  self.table_name = 'sum_n_product_with_tab(:dynamic_input)'
end

Answer 1

Create a view containing the data you need and then you can easily create an ActiveRecord model to access it.

You haven't provided specific details of your data but as a simple example, create your view in Postgres to collect your data;

  create or replace view data_trails as
    select t.*, td.trail_date from trails t
    join trail_dates td on (td.trail_id = t.id)

Then create your model

  class DataTrail < ActiveRecord::Base
    scope :on_date, -> (date) { where(trail_date: date) }
  end

  DataTrail.on_date(Date.today)

You can find more information in the Enterprise Rails book. It's getting a little dated now but the principles are sound.

http://enterpriserails.chak.org/full-text/chapter-11-view-backed-models

Answer 2

create a dummy table with the same columns as your function output:

CREATE TABLE report.compliance_year (
year TIMESTAMP,
compliance NUMERIC(20,2),
fund_id INT);

Create your model:

class Visualization::ComplianceByYear < ActiveRecord::Base
    self.table_name = 'report.compliance_year'
    def compliance_by_year(fund_id)
        Visualization::ComplianceByYear.find_by_sql(["
            SELECT year, compliance, fund_id
              FROM report.usp_compliance_year(ARRAY[?])", fund_id])
    end
end 

Reference it in your controller and populate it with the function call results:

def visualizations
  @compliancebyyear = Visualization::ComplianceByYear.new()
  @compliancefunds = @compliancebyyear.compliance_by_year(current_group.id)
end