I’m interested in this as well… unfortunately, it looks to be a bit trickier than using a simple fragment.
For example, here’s the above query for users:
iex(26)> from(u in fragment("? AS OF SYSTEM TIME '-5m'", ^User.__schema__(:source)), select: [:name, :email])
#Ecto.Query<from f0 in fragment("? AS OF SYSTEM TIME '-5m'", ^"users"),
select: [:name, :email]>
iex(27)> |> Repo.all()
[debug] QUERY ERROR db=0.0ms queue=5.4ms idle=1805.7ms
SELECT f0."name", f0."email" FROM $1 AS OF SYSTEM TIME '-5m' AS f0 ["users"]
** (Postgrex.Error) ERROR 42601 (syntax_error) at or near "1": syntax error
query: SELECT f0."name", f0."email" FROM $1 AS OF SYSTEM TIME '-5m' AS f0
hint: try \h <SOURCE>
source SQL:
SELECT f0."handle", f0."page_title" FROM $1 AS OF SYSTEM TIME '-5m' AS f0
^
(ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1047: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:945: Ecto.Adapters.SQL.execute/6
(ecto 3.10.3) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
(ecto 3.10.3) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
iex:27: (file)
`
Note that the generated query is not correct:
-- This is invalid
SELECT f0."name", f0."email" FROM $1 AS OF SYSTEM TIME '-5m' AS f0
The SYSTEM TIME needs to place the f0 immediately after the FROM. The correct variant would be:
SELECT f0."name", f0."email" FROM $1 f0 AS OF SYSTEM TIME '-5m'
I’m not sure how to go about modifying this or even if it’s possible.
