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