up vote 8 down vote favorite
1

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?

share|improve this question
    
AFAIK there's no built-in way to write SQL functions via Rails, but depending on the functions, they might be better refactored as scopes or other model methods. – eirikir Aug 11 '15 at 23:20
    
However, you could simply write a class in the lib directory that defines SQL functions. You can execute arbitrary SQL via ActiveRecord::Base.connection.execute. – eirikir Aug 11 '15 at 23:24
    
Are you looking for some kind of "PL/Ruby" with ActiveRecord support? – Craig Ringer Aug 11 '15 at 23:39
up vote 20 down vote accepted

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.

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

share|improve this answer
up vote 0 down vote

If your only requirement is creating them somewhere in your Rails app, this is possible through ActiveRecord::Base.connection.execute, which you can use to execute raw SQL queries.

stmt = 'CREATE FUNCTION...'
ActiveRecord::Base.connection.execute stmt

You would then call the function using ActiveRecord::Base.connection.execute as well (I'd imagine you'd have methods in your model to handle this).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.