Coming from the MSSQL world I'm used to performing some set-based operations on the database backend, especially when it makes performance sense to do so. If you search for "rails stored procedure" you typically get a lot of nonsense about DHH not liking stored procedures and not a lot of practical examples.

I needed to essentially have a parameterized view (that's what we called it in SQL Server) and I was delighted when I found that Postgres supports it right out of the box - not to mention its superior temporal functions. The next challenge was Rails of course. I'm stepping outside of the typical ActiveRecord model pattern here and while I'm sure there are better ways to execute on this here's my first practical working version of integrating a parameterized view (ahem, table returning function) from Postgres into Rails 3.

(And for the curious: yes you can SELECT * FROM yourfunction(91) WHERE somecolumn > 42 just like a parameterized view.)

So first I have my migration in which I create my function:

$ rails g migration CreateStockHourlyPerformanceFunction

Then the actual function itself:

# ./db/migrations/TIMESTAMP_create_stock_hourly_performance_function.rb

class CreateStockHourlyPerformanceFunction < ActiveRecord::Migration  
  def up
    execute <<-SPROC
CREATE OR REPLACE FUNCTION stock_hourly_performance(integer) RETURNS TABLE(time_block timestamp, average integer) AS $$  
SELECT  
  block AS time_block
  ,MAX(block_avg)::integer AS average
FROM  
  (SELECT
    date_trunc('hour', created_at) AS created_at_trunc
    ,AVG(value) AS block_avg
  FROM stock_values
  WHERE stock_id = $1
  GROUP BY date_trunc('hour', created_at)) AS x,
  (SELECT
    ('today'::date+'1 hour'::interval*q)::timestamp AS block
  FROM generate_series(0, extract('hour' from localtimestamp)::integer) AS q) AS y
WHERE created_at_trunc > block - '1 hour'::interval AND created_at_trunc <= block  
GROUP BY 1 ORDER BY 1  
$$ LANGUAGE SQL;
SPROC  
  end

  def down
    execute "DROP FUNCTION IF EXISTS stock_hourly_performance(integer)"
  end
end

Then don't forget to migrate!

$ rake db:migrate  
$ RAILS_ENV=test rake db:migrate

And then the other interesting part - how do we integrate with the Rails environment? I chose not to use ActiveRecord because it doesn't make a lot of sense here. I have a feeling I'd get better results if I went with DataMapper but I'm not ready to configure another data access layer just yet. Also it turns out that hourly performance won't be the only time we need the Performance class - this class will gain some more static constructor methods over time as the project evolves.

 # ./models/performance.rb  
class Performance  
  attr_accessor :time_block, :average
  def initialize(time_block, avg)
    @time_block = time_block.is_a?(DateTime) ? time_block : DateTime.parse(time_block)
    @average = avg.to_i
  end
  class << self
    def hourly_for_stock(stock_id)
      result = ActiveRecord::Base.connection.execute("SELECT * FROM stock_hourly_performance(#{stock_id})")
      if result.count == 0
        stock = Stock.select(:par_value).find(stock_id)
        return [Performance.new(DateTime.now.at_beginning_of_day, stock.par_value)]
      end
      result.map { |r| Performance.new(r["time_block"], r["average"]) }
    end
  end
end