Psql unnest in ecto

Have you seen this Ecto.Query — Ecto v3.14.0?

Thanks for your input!

The solution I came up with is to use a CTE to hold the dynamic values from the unnest:

union_query = 
  "cte_name"
  |> with_cte("cte_name", as: fragment("select my_column from unnest(ARRAY[5, 10, 15]) my_column"))
  |> select([c], %{ my_column: fragment("my_column") }

This query is able to be unioned

Glad you managed to achieve what you wanted @CodeBumpkin

Also look at transaction and Ecto multi, to use only one trip to the database.

It’s been some time since this was posted but with the latest Ecto v3.9 at least there’s a way to do it without a CTE (although not too pretty either). Can’t say about previous Ecto versions.

data = [1,2,3]
q1 = 
  from(data in fragment(“select mycolumn from unnest(?::int[]) as x(mycolumn)”, ^data),
    select: data.mycolumn
   )
q2 = from(table in MyTable, select: table.mycolumn)
q3 = union_all(q1, ^q2)
values = Repo.all(q3)