How can execute multiple statements in one query with Rails?

activerecord, postgresql, ruby, ruby-on-rails, ruby-on-rails-3

Solution

It should work out of the box with PostgreSQL, checked with pg gem and rails 3.2:

class Multitest < ActiveRecord::Migration
  def up
    execute <<-SQL
      create table x(id serial primary key);
      create table y(id serial primary key, i integer);
    SQL
  end

  def down
  end
end

On a side note, manipulating `schema_migrations` directly looks strange.

Problem

I am using Ruby on Rails with ActiveRecord and PostgreSQL. How can i execute multiple sql queries? I need it for running a custom migration script, eg: ``` Foo.connection.execute <<-SQL.split(';').map(&:strip).join delete from metadata where record_type = 'Foo'; TRUNCATE table1 RESTART IDENTITY; TRUNCATE table2 RESTART IDENTITY; delete from schema_migrations where version > '20120806120823'; SQL ``` I am not accepting data from a user, so I'm not worried about sql-injection. Something like `CLIENT_MULTI_STATEMENTS` in MySQL maybe ? From the MySQL/PHP docs: CLIENT_MULTI_STATEMENTS: Tell the server that the client may send multiple statements in a single string (separated by “;”). If this flag is not set, multiple-statement execution is disabled. See the note following this table for more information about this flag.

Original source

Related problems