Hi,

I have a multi-tenant system and I use Postgres Schemas to isolate data for each tenant. When a tenant signs up I run a set of migrations using the Migrator. I would like to create a MATERIALIZED VIEW for each Tenant that joins a table from the tenant’s schema with a public schema table.

defmodule Migrations.AddUserView do
  use Ecto.Migration

  def up do
    execute """
    CREATE MATERIALIZED VIEW users 
    AS SELECT fullname, email, email_verified, mobile, mobile_verified, roles.display 
    FROM PUBLIC.users, TENANT.tenant_users, TENANT.roles WHERE PUBLIC. users.id = 
    TENANT.tenant_users.user_id 
    AND  PUBLIC.roles.id =TENANT.tenant_users.role_id;
    """

    
  end

  def down do
    execute "DROP MATERIALIZED VIEW users;"
  end
end

To create the VIEW I need to pass the schema name to the migration so it can access the correct tables. Does anyone know if this is possible?

Regards,

Andrew