Send an the generated query from an Ecto.Query to a postgres function

I need to push an Ecto.Query (by it’s fullness including all the arguments interpolated) to a PostgreSQL function.
The query is generated behind a GraphQL API at the resolver level so it can by anything from the simplest select, to a complex query with joins, sub-queries and more.

Initially I was thinking to get the raw query from the adapter then manually query via the adapter:

raw_query = Ecto.Adapters.SQL.to_sql(:all, Repo, query)
Ecto.Adapters.SQL.query(Repo, raw_query)

but not only the raw_query is not a string and I need to interpolate the params, not sure if it’s the best way to go forward, even do as it is not it seems that is the only way.

Tried via fragment/dynamic but could not get my head around it to make it work, especially that I need to send the query as string to the PostgreSQL function but was unable to find a way to injecting the Ecto.Query inside the fragment.

Taking the most basic query:
from t0 in "table", where: t0<0, select: t0

How can one push this into a PostgreSQL function ?, resulting this query in the database:
SELECT postgress_function("SELECT t0 FROM \"table\" AS t0 WHERE (t0 < 0)");

Any other ideas in this direction ?

Is this what you’re looking for?

I tried that, visible in my first example and I still think it’s the only way to go forward, even do I need to interpolate the params by myself, thus taking in consideration or sometimes guessing their type:

Take this example:

var = 0                                             
query = from t0 in "table", where: t0<^var, select: t0  
raw_query = Ecto.Adapters.SQL.to_sql(:all, EctoRepo, query)
{"SELECT t0 FROM \"table\" AS t0 WHERE (t0 < $1)", [0]}

And I can’t send that to the PostgreSQL function, also as I said before I am worried by the need of doing the string interpolation.

I tried that, visible in my first example

Oops, need to improve my reading skills, sorry about that!

It does indeed appear that there’s no way within the Elixir ecosystem to obtain a “standalone” string of the resulting query: even postgrex relies on sending the prepared query and its parameters separately. This was also touched on here: Ecto query into Plain SQL

send that to the PostgreSQL function

Could you maybe expand on this part? By “send” do you literally mean to send to a different node, or just to give it as a param? Does something like this work?

var = 0                                             
query = from t0 in "table", where: t0<^var, select: t0
Repo.all(from q in query, select: fragment("postgress_function(?)", ^q))

Based on

By “send” I mean to give it as a param to another schema-less query, that only calls a PostgreSQL function.

Thanks for your suggestions, unfortunately your suggestion based on Mike_Andr post does not work (not allowed by Query Compiler) and I get this error:

 Request: POST /
** (exit) an exception was raised:
    ** (Ecto.Query.CompileError) only one select expression is allowed in query

The reason I need to send the full query as it was executed by PostgreSQL is that the postgress_function() does some EXPLAIN and some more analysis on the query.

I am aware that this is not a use-case encountered by the majority of us and definitely not something tested or considered by either Ecto or Postgrex, but worth exploring before starting to look at other ways to accomplish the goal outside Ecto/Postgrex, maybe by changing the whole logic behind this analysis stuff :thinking:.

I realized that composing queries only one select is allowed, thus the error so I changed it to:

var = 0                                             
query = from t0 in "table", where: t0<^var
Repo.all(from q in query, select: fragment("postgress_function(?)", ^query))

And got some expected error, definitely not the way to go:

** (DBConnection.EncodeError) Postgrex expected a binary, got #Ecto.Query<from t0 in "table">. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
    (postgrex) lib/postgrex/type_module.ex:721: Postgrex.DefaultTypes.encode_params/3
    (postgrex) lib/postgrex/query.ex:62: DBConnection.Query.Postgrex.Query.encode/3
    ... (truncated)

Based on How to do multi selects in Ecto, using a subquery may help?

Something like:

var = 0                                             
query = from t0 in "table", where: t0<^var, select: t0
Repo.all(from q in subquery(query), select: fragment("postgress_function(?)", ^q))

Thanks @david_ex for all your time and suggestions. Subqueries do help in this case but I think we can agree that we’re reached at the bottom/low level of the implementation and at this point it’s definitely a dead end.

Will see if I can add an EXPLAIN on the original query via fragments/dynamic, run it normally via Repo.all() then parse the results in elixir instead of the PostgreSQL side (the pseudo postgress_function I mentioned).

Will get back with a new question/topic in the forum if I don’t figure out how to do EXPLAIN SELECT ... via Ecto. Thanks again for your time.

I will answer that right away - you cannot unless you will use raw SQL query in form of Repo.query/2. And even then I am not 100% if this will work.

Thanks, pretty much had the same feeling and I started looking at the Ecto repo with the idea that I might need to do a pull with the implementation, but that’s too much time invested at this point.
It’s time to get my Product Manager suit on and start talking about this “feature” at another level :smirk:

If you just want to track long queries in PostrgreSQL in your log aggregating service then you probably want auto_explain section from configuration.

@ marisradu: could you find a solution at the end?