我可以在Ruby on Rails上编写PostgreSQL函数吗?

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.

我们正在启动一个基于Ruby on Rails的项目。我们曾经使用Perl和PostgreSQL函数，使用Rails和活动记录，我还没有看到如何在PostgreSQL中创建函数，并使用活动记录和模型保持记录。

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.

我知道我们可以在PostgreSQL中手动创建它，但是使用活动记录的“魔法”是可以用所有模型重新创建数据库。

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

是否有办法使用Rails创建PostgreSQL函数并将其保存在模型中?

2 个解决方案

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

我知道我们可以在PostgreSQL中手动创建它，但是使用活动记录的“魔法”是可以用所有模型重新创建数据库。

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.

告诉我，您确实在寻找一种方法来将PostgreSQL函数与常规的Rails迁移过程集成在一起，并对诸如db:schema:load之类的任务进行Rake。

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.

您需要使用独立的上下方法，而不是单一的更改方法，因为ActiveRecord不知道如何应用，更不用说逆转函数创建了。你使用连接。执行以将原始函数定义提供给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:

然而,模式。rb和通常使用模式的Rake任务。rb(例如db:schema:load和db:schema:dump)不知道如何处理PostgreSQL函数和ActiveRecord不理解的其他东西。但是有一种方法可以解决这个问题，您可以选择使用一个结构。sql文件而不是模式。rb通过设置:

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:

在你的配置/应用程序。rb文件。之后，db:migrate将写一个db/结构。sql文件(它只是一个没有数据的PostgreSQL数据库的原始sql转储)而不是db/schema.rb。您还将使用不同的Rake任务来处理结构。

db:structure:dump instead of db:schema:dump
db:结构:转储代替db:模式:转储
db:structure:load instead of db:schema:load
db:结构:负载代替db:模式:负载

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

这种方法还允许您在数据库中使用ActiveRecord不理解的其他东西:检查约束、触发器、非简单列默认值……

#2

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.

如果您的惟一需求是在Rails应用程序的某个地方创建它们，那么通过ActiveRecord::Base.connection，这是可能的。可以使用execute执行原始SQL查询。

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

然后使用ActiveRecord: Base.connection调用该函数。执行也一样(我想您的模型中应该有方法来处理这个问题)。

#1