How to make attribute setter send value through SQL function

Question

I'm trying to make an attribute setter in an ActiveRecord model wrap its value in the text2ltree() postgres function before rails generates its sql query.

For example,

post.path = "1.2.3"
post.save

Should generate something like

UPDATE posts SET PATH=text2ltree('1.2.3') WHERE id = 123 # or whatever

What's the best way of doing this?

Answer 1

EDIT: To achieve exactly what you are looking for above, you'd use this to override the default setter in your model file:

def path=(value)
  self[:path] = connection.execute("SELECT text2ltree('#{value}');")[0][0]
end

Then the code you have above works.

I'm interested in learning more about ActiveRecord's internals and its impenetrable metaprogramming underpinnings, so as an exercise I tried to accomplish what you described in your comments below. Here's an example that worked for me (this is all in post.rb):

module DatabaseTransformation
  extend ActiveSupport::Concern

  module ClassMethods
    def transformed_by_database(transformed_attributes = {})

      transformed_attributes.each do |attr_name, transformation|

        define_method("#{attr_name}=") do |argument|
          transformed_value = connection.execute("SELECT #{transformation}('#{argument}');")[0][0]
          write_attribute(attr_name, transformed_value)
        end
      end
    end
  end
end

class Post < ActiveRecord::Base
  attr_accessible :name, :path, :version
  include DatabaseTransformation
  transformed_by_database :name => "length" 

end

Console output:

1.9.3p194 :001 > p = Post.new(:name => "foo")
   (0.3ms)  SELECT length('foo');
 => #<Post id: nil, name: 3, path: nil, version: nil, created_at: nil, updated_at: nil> 

In real life I presume you'd want to include the module in ActiveRecord::Base, in a file somewhere earlier in the load path. You'd also have to properly handle the type of the argument you are passing to the database function. Finally, I learned that connection.execute is implemented by each database adapter, so the way you access the result might be different in Postgres (this example is SQLite3, where the result set is returned as an array of hashes and the key to the first data record is 0].

This blog post was incredibly helpful:

http://www.fakingfantastic.com/2010/09/20/concerning-yourself-with-active-support-concern/

as was the Rails guide for plugin-authoring:

http://guides.rubyonrails.org/plugins.html

Also, for what it's worth, I think in Postgres I'd still do this using a migration to create a query rewrite rule, but this made for a great learning experience. Hopefully it works and I can stop thinking about how to do it now.