Can I write PostgreSQL functions on Ruby on Rails?

Question

We are starting a project based on Ruby on Rails. We used to work with Perl and PostgreSQL functions, and with Rails and Active Record I've not seen how we are supposed to create functions in PostgreSQL and keep the record with Active Record and models.

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.

Is there any way to create the PostgreSQL function using Rails and keep it in the models?

Answer 1

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, ...