schema qualified queries in ecto

Question

I'm trying to use ecto with postgres schemas, but the queries are directed to the public schema as table names when used from repo.whatever.

I can pass valid schema qualified queries as sql with query/3 like this:

 query(Repo, "SELECT * FROM schema.table", [])

And get the results from the table in the schema.

This:

 Repo.all(%Ecto.query{from: {"schema.table", model}})

Results in the expected sql which is:

 SELECT a0.foo FROM schema.table as a0

However, the query results in an error that the table schema.table cannot be found. If postgrex sends "schema.table" to postgres, it should be interpreted as a schema qualified table name as in the direct query, right? Is postgrex parsing the table name somehow before sending it?

michalmuskala michalmuskala · Accepted Answer · 2015-09-30T14:55:34

Ecto.Query struct has a special member called prefix. With PostgreSQL it allows you to choose a specific schema:

my_query = from(m in Model, where: m.foo == ^bar)
%{my_query | prefix: "schema"}

This will make the query (and all the produced models when reading) to use this schema.

You can also set prefix for a single model with:

model = %Model{}
Ecto.Model.put_meta(model, prefix: "schema")

This will make all the subsequent repo operations with this model (like preloading other models or updating this model) use the specified schema. As I said above - using the prefix for a read query (like one/2, all/2, etc.) will also set the prefix for the model.

I think the documentation for this feature is missing for some reason. I'm not sure exactly why, probably just an oversight.

schema qualified queries in ecto

1 Answers