[Proposal] Ecto.Query to have a way to create entire raw queries

[cevado]

I was trying to use Ecto.Query with jsonb_to_recordset() function from postgres and it's kinda impossible. I think either subquery or a new macro(idk, raw?) could allow to pass an entire raw sql that only could be composed as a subquery or cte.

i'm trying to have the following query written with Ecto.Query api:

update table as t

join jsonb_to_recordset(...) as r(id1 uuid, id2 uuid) on t.id = r.id1

set t.external_id = r.id2, updated_at = now()

i tried:
from(r in fragment("jsonb_to_recordset(?)", ^list_of_maps), as: :"r(id1 uuid, id2 uuid)", select: %{id1: r.id1, id2: r.id2})
and

from(r in fragment("jsonb_to_recordset(?) as r(id1 uuid, id2 uuid)", ^list_of_maps),, select: %{id1: r.id1, id2: r.id2})

but the as: option on ecto is just for the ecto api, doesn't become part of the query, and the second one produce wrong syntax sql(with 2 as statements).

[Felipe Stival]

I think mostly the same logic used by `Ecto.Query.values/2` can be used for this case, the behaviours are very similar. 

But I'm not sure how the API could look like on the Ecto.Query side, I'm not a big fan of adding 4 new postgres-only functions there. I wonder if we could somehow support these json functions **on** `values/2`.

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/elixir-ecto/a1f4d60e-0b45-4b99-bc44-c81f5deace66n%40googlegroups.com.

[cevado]

the problem is not the actual function, but lacking a way to pass a list of fields and types to the AS statement.

I was thinking something like fragment(that would handle interpolation in a safe way), but that could be used only with cte and subquery.

this way any special sql(doesn't matter the database) statements could be integrated to the actual Ecto.Query api, without Ecto actually needing to mimic every special function for different flavors of sql.

[Felipe Stival]

My point is that `values` does precisely this. It passes types and columns names to AS. 

To view this discussion visit https://groups.google.com/d/msgid/elixir-ecto/04132301-7ee9-40eb-a612-66253e59cf18n%40googlegroups.com.

[Felipe Stival]

Actually, `values` passes only the column names to `AS`:

This code:

```

from(p in values(list_of_maps, %{a: :integer, b: :integer}))
|> select([:a, :b])
|> Repo.all()

```

Runs the following:

```

22:44:10.851 [debug] QUERY OK db=0.3ms queue=0.6ms idle=82.0ms
SELECT v0."a", v0."b" FROM (VALUES ($1::bigint,$2::bigint),($3::bigint,$4::bigint)) AS v0 ("a","b") [1, 2, 3, 4]

```

So I'm not sure if we can use the same code here.

[cevado]

oh, i get it.... i understood you were suggesting to create a new thing that "works like" values but that wraps this function inside.

i was thinking something closer to what i mentioned because that would be generic and open enough to avoid making Ecto.Query api too specific.

using values work if the list of stuff is small, because the more stuff you send the bigger the number of parameters in the query, and postgres has a limit for that.
and also that makes the query increasingly slower, because postgres needs to parse each parameter individually.

with jsonb_to_recordset i gonna pass a single json that is the list of maps, it's a single parameter and just one thing for postgres do parse.

now i'm thinking if i could pass a fragment to values would be a good approach too

[Greg Rychlewski]

I believe you can get something like this to work if you write the query in this manner (sorry i simplified your example for my own testing)

from (f in fragment("select * from jsonb_to_recordset(...) as r(id1 int, id2 int)", ^...), select: %{id1: f.id1, id2: f.id2})

The main thing with fragment sources is they need to return a table value. So having `jsonb_to_recordset` alone does not work. You need to add your own select so it returns a table.

[cevado]

I didn't know that i can write a full query into fragment, so it actually does what i was asking here... 🤯

[Greg Rychlewski]

If you try it and it works for you, we can update the docs with an example. When we made it we had a few common requests in mind and put those as examples. But we're happy to include new uses as well.

[cevado]

it works. I can open a pr later today. i think this also can be used on some improvements to a insert_all/update_all queries. i'll come up with some good examples.

[Greg Rychlewski]

Awesome, thank you!

[Benjamin Milde]

I have a macro for jsonb_to_recordset posted on the forum: https://elixirforum.com/t/postgres-values-in-query/53635/2

Only downside compared to the ecto values is that it needs to know the as part at compile time. Bit the upside is that it needs just a single parameter on the query.