Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I write PostgreSQL functions on Ruby on Rails?

Tags:

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?

like image 826
MaxJRB Avatar asked Aug 11 '15 23:08

MaxJRB


People also ask

How do I connect to a Postgres database Ruby?

To connect to PostgreSQL, set the Server, Port (the default port is 5432), and Database connection properties and set the User and Password you wish to use to authenticate to the server. If the Database property is not specified, the data provider connects to the user's default database.

Is Postgres function transactional?

Yes, functions are transactional, even if written in LANGUAGE SQL .


Video Answer


1 Answers

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

like image 142
mu is too short Avatar answered Sep 23 '22 20:09

mu is too short