CODEWITHSUNDEEP
ruby-on-railspostgresql
Ricardo Acras
Ricardo Acras

Reputation: 36244

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

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:

None have worket.

Upvotes: 3

Views: 5145

Answers (7)

Serhii Potapov
Serhii Potapov

Reputation: 3970

We designed a gem to make ActiveRecord work with PostgreSQL. Here is a small usage example: http://greyblake.com/blog/2012/09/06/pg-power-activerecord-extension-for-postgresql/

Upvotes: 0

Sam
Sam

Reputation: 2969

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

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

Upvotes: 4

Ricardo Acras
Ricardo Acras

Reputation: 36244

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

Upvotes: 3

Raimonds Simanovskis
Raimonds Simanovskis

Reputation: 2948

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

Upvotes: 3

Patryk Kordylewski
Patryk Kordylewski

Reputation: 1269

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;

Upvotes: 0

Hates_
Hates_

Reputation: 68671

set_table_name "Financial.budget"

Upvotes: 0

Ian Terrell
Ian Terrell

Reputation: 10866

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

Upvotes: 0

Related Questions