How to set PostgreSQL table names in Rails with schema information?

Question

I have to connect my rails app in a legacy Postgre database. It uses schemas so in a SQL its is common to use something like

SELECT * FROM "Financial".budget

I want to write a Budget model but I don't know how to set the table name in this case. I've tried the following:

• set_table_name 'budget'
• set_table_name '"Financial".budget'

None have worket.

Answer 1

Look at your logs -- what SQL is Rails generating with your various options?

Answer 2

set_table_name "Financial.budget"

Answer 3

Perhaps extending the search path with your schema will help you?

SET SEARCH_PATH TO "Financial", public;

Then you could write your query unqualified without the schema:

SELECT * FROM budget;

Answer 4

I had similar issue with Oracle adapter. By default ActiveRecord always quotes table names in SQL and therefore if you specify

set_table_name "Financial.budget"

then generated SQL will be

SELECT * FROM "Financial.budget"

which will not work.

To solve this issue you need to monkey patch PostgreSQL adapter. Put into your environment.rb or in separate initializer the following code:

ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do
  # abstract_adapter calls quote_column_name from quote_table_name, so prevent that
  def quote_table_name(name)
    name
  end
end

Now you should define in your model class

set_table_name "Financial.budget"

and generated SQL will be

SELECT * FROM Financial.budget

Answer 5

Now, this bug seems to be solved in 2-3-stable. Take a look at this post

Answer 6

ActiveRecord::Base.establish_connection(
    :schema_search_path => 'Financial,public'
)

If you're using Postgres, the above is probably "good enough" for most situations.