How to Write Postgresql Functions on Ruby on Rails

Can I write PostgreSQL functions on Ruby on Rails?

This part of your question:

I know we can create it manually in PostgreSQL, but the "magic" with Active Record is that the database can be recreated with all the models.

tells me that you're really looking for a way to integrate PostgreSQL functions with the normal Rails migration process and Rake tasks such as db:schema:load.

Adding and removing functions in migrations is easy:

def up
  connection.execute(%q(
    create or replace function ...
  ))
end

def down
  connection.execute(%q(
    drop function ...
  ))
end

You need to use separate up and down methods instead of a single change method because ActiveRecord will have no idea how to apply let alone reverse a function creation. And you use connection.execute to feed the raw function definition to PostgreSQL. You can also do this with a reversible inside change:

def change
  reversible do |dir|
    dir.up do
      connection.execute(%q(
        create or replace function ...
      ))
    end
    dir.down do
      connection.execute(%q(
        drop function ...
      ))
    end
  end
end

but I find that noisier than up and down.

However, schema.rb and the usual Rake tasks that work with schema.rb (such as db:schema:load and db:schema:dump) won't know what to do with PostgreSQL functions and other things that ActiveRecord doesn't understand. There is a way around this though, you can choose to use a structure.sql file instead of schema.rb by setting:

config.active_record.schema_format = :sql

in your config/application.rb file. After that, db:migrate will write a db/structure.sql file (which is just a raw SQL dump of your PostgreSQL database without your data) instead of db/schema.rb. You'll also use different Rake tasks for working with structure.sql:

• db:structure:dump instead of db:schema:dump
• db:structure:load instead of db:schema:load

Everything else should work the same.

This approach also lets you use other things in your database that ActiveRecord won't understand: CHECK constraints, triggers, non-simple-minded column defaults, ...

What is the best way to call a Postgres stored function in Rails?

There is no special method in ActiveRecord, you need to use SQL. You can just do something like

Post.connection.execute("select version();").first 
=> {"version"=>"PostgreSQL 10.5 on x86_64-apple-darwin17.7.0, compiled by Apple LLVM version 9.1.0 (clang-902.0.39.2), 64-bit"}

This will return a hash per row where the keys are the column-names, and the values the corresponding values. So for this specific example, I know this will only return one row so I do first to retrieve the first row immediately. If you would just want to retrieve the version immediately, you could also write

version = Post.connection.execute("select version();").first.values.first 
=> "PostgreSQL 10.5 on x86_64-apple-darwin17.7.0, compiled by Apple LLVM version 9.1.0 (clang-902.0.39.2), 64-bit"

How do you define postgres functions in Rails and what is their scope and lifetime?

Once created, a function in PostgreSQL is persisted permanently. Like other objects it lives inside a schema. Unless you schema-qualify the function name, it is only found if this schema is in the search_path of your current session (and no hidden by another function of the same in a different schema).

In a general-purpose database, you would create your functions in the default schema public, which is in the search path by default. In a more sophisticated setup, you might have a dedicated schema for functions or a dedicated schema per user. The search_path would be set accordingly.

Use CREATE OR REPLACE FUNCTION ... instead of just CREATE FUNCTION ... so you can replace the body of an existing function (without changing parameters). Recent related answer by @Pavel Stehule on dba.SE.

Be aware of the slightly different effects of time zone names, abbreviations, and numeric offsets. Related:

• Time zone names with identical properties yield different result when applied to timestamp
• PostgreSQL - how to render date in different time zone?

How to make attribute setter send value through SQL function

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.

---

Related Topics