How can I retrieve the set returned from a STR into a model via ecto?

Question

I have a set returning function on postgreSQL e.g.

CREATE FUNCTION set_ret_func(foo int, bar int) 
  RETURNS TABLE(total bigint, result bigint) AS $$
    SELECT a.val + $1 as total, b.val + $2 as result 
    FROM a, b
  $$ LANGUAGE SQL;

this function provides me with a "flexible view" that I can easily call as:

SELECT * from set_ret_func('30'::int, '89'::int)

looking around I found that polymorphic associations could be called via Ecto.Repo doing something like

from( x in {"table_name", Model}, select: x.total )
|> Repo.all

This made me wonder if I could either use Ecto.Query, or the fragment/1 in Ecto.Query.API in the from/2 so I was able to call my set returning function into a Schema (they will both match on column_name, type) on something that according to my imagination would have looked something like:

from( srf in {fragment("set_ret_func(?::int, ?::int)", var1, var2), Model},
   select: srf.total)

Is there an actual way to call this STRs and cast them into a model?

Answer 1

Honestly, the easiest way to do this is Ecto.Adapters.SQL.query. You can still load it into the model. Here's a quick snippet that should be approximately correct:

with {:ok, %{columns: cols, rows: rows}} <- Ecto.Adapters.SQL.query(Repo, "SELECT * FROM set_rec_func(?,?)", [var1, var2])
   fields = Enum.map(cols, &String.to_existing_atom/1) do
   for row <- rows, values = Enum.zip(fields, rows) do
     struct(Model, values) |> Ecto.put_meta(state: :loaded)
   end
end

Though this isn't exactly what your ecto query tried to do, which was select just a single field and not the entire model. There's actually not that much model magic that happens in Ecto. All the real magic happens in the Ecto.Query API and in the Ecto.Changeset API. To prevent future confusion, in fact, Ecto 2.0 (coming in a matter of days) is dropping the "Model" word completely. They're all just normal structs tied to some schema metadata.