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

That's the original route that I went, but in my query I have to join about five tables that all filter on a single date and do computation based on that date... So I need to be able to pass in some kind of variable or use a function to accomplish this (I think). — Helios de Guerra
– Helios de Guerra, Commented Mar 10, 2014 at 19:53
I suppose I could, though its a 100+ line query, and I'd have to keep my query in sync with the database function the database guys have implemented. — Helios de Guerra
– Helios de Guerra, Commented Mar 10, 2014 at 20:27
Or you can use arel Model.select(Arel::Nodes::NamedFunction.new('sum_n_product_with_tab', 6)).to_sql — Andy Henson
– Andy Henson, Commented Mar 15, 2014 at 0:30

Andy Henson · Accepted Answer · 2014-03-15 00:53:20Z

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

answered Mar 15, 2014 at 0:53

Andy Henson

4074 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lem Ko Over a year ago

Views don't do it for me because the function needs the argument for a subquery inside the function. If I forgo the subquery and just filter after the function executes (as suggested here), then the function would be too slow as it will have to calculate for all rows of a table.

Caullyn · Accepted Answer · 2015-08-26 16:47:08Z

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

Collectives™ on Stack Overflow

Use Postgres Function as ActiveRecord Model

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related