Use Postgres Function as ActiveRecord Model

Question

I created a Postgres function that I use to execute a complex query that combines many tables that all should be filtered by a dynamic date field.

The function works fine and allows me to execute 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 can I use this return table as a Rails / Ruby model where the function argument 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 that contains data, and you can easily create an ActiveRecord model to access it.

You did not provide specific data for your data, but as a simple example, create your own 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) 

For more information, see the Enterprise Rails book. It's a bit outdated, but the principles sound.

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

Answer 2

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

 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 

Link to it in your controller and fill it with the results of the function call:

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