I’ve defined a table in postgresql to store a NonBinarySearchTree. The table is named nodes.
Each row contains an id, and optionally a parent_id (as a self referencing foreign key).

For ease and convenience i’ve defined two postgresql functions all_ancestors_of(id) and all_descendants_of(id)

The migration/definition of the ancestors-one looks like this

  def up do
     execute """
    create function all_ancestors_of(node_id UUID) returns SETOF nodes AS
    'WITH RECURSIVE ancestors AS (
      SELECT
        *
      FROM
          nodes
      WHERE
          nodes.id = all_ancestors_of.nodes_id

      UNION

      SELECT
          parents.*
      FROM
          nodes AS parents
      INNER JOIN ancestors a ON a.parent_id = parents.id
    ) SELECT * from ancestors where id != all_ancestors_of.node_id;
    ' LANGUAGE SQL;
    """
  end

This works beautifully in SQL, but Im struggling to use these functions via Ecto.

How do I get the list of nodes (i have a schema for them) without resorting to

    res = Ecto.Adapters.SQL.query!(Repo, "SELECT * from all_ancestors_of($1)", [uuid])
    Enum.map(res.rows, &Repo.load(Node, {res.columns, &1}))

?

I’m was hoping to be able to do something like

query = from e in fragment(all_ancestors_of($1), [uuid])
nodes = Repo.all(query)