ruby-on-railsruby-on-rails-4database-migrationdatabase-view

Rails migration for altering existing database view


I have a view named my_view which I created using the following migration.

class CreateMyView < ActiveRecord::Migration
  def change
    execute <<-SQL
      drop view if exists my_view
    SQL

    execute <<-SQL
      CREATE OR REPLACE VIEW my_view AS 
      SELECT 
        t1.wfs_id,
        t1.step_id,
        t1.status,
        t1.applied_by,
        t2.created_at,
        t2.is_wfs_end,
        t2.app_status AS flowstep
       FROM table1 t1
         JOIN table2 t2 ON t1.wfs_id = t2.wfs_id
      WHERE t1.del_flag = false;
    SQL
  end
end

now I need another field, say my_new_field from table1 to be available in my_view. But I have no Idea how to write the migration for this. Any help much appreciated. Thanks


Solution

  • What about simply recreating the view:

    class ChangeMyView < ActiveRecord::Migration   
      def change
        execute <<-SQL
          drop view if exists my_view
        SQL
    
        execute <<-SQL
          CREATE OR REPLACE VIEW my_view AS 
          SELECT 
            t1.wfs_id,
            t1.step_id,
            t1.status,
            t1.applied_by,
            t1.my_new_field,
            t2.created_at,
            t2.is_wfs_end,
            t2.app_status AS flowstep
          FROM table1 t1
            JOIN table2 t2 ON t1.wfs_id = t2.wfs_id
          WHERE t1.del_flag = false;
       SQL 
      end
    
    end