Ecto.Function

Helper macro for defining macros that simplifies calling DB functions.

Installation

The package can be installed by adding ecto_function to your list of dependencies in mix.exs:

def deps do
  [
    {:ecto_function, "~> 1.0.1"}
  ]
end

The docs can be found at https://hexdocs.pm/ecto_function.

Usage

When you use a lot of DB functions inside your queries then this probably looks like this:

from item in "items",
  where: fragment("date_trunc(?, ?)", "hour", item.inserted_at) < fragment("date_trunc(?, ?)", "hour", fragment("now()")),
  select: %{regr: fragment("regr_sxy(?, ?)", item.y, item.x)}

There are a lot of fragment calls which makes code quite challenging to read. However there is way out for such code, you can write macros:

defmodule Foo do
  defmacro date_trunc(part, field) do
    quote do: fragment("date_trunc(?, ?)", ^part, ^field)
  end

  defmacro now do
    quote do: fragment("now()")
  end

  defmacro regr_sxy(y, x) do
    quote do: fragment("regr_sxy(y, x)", ^y, ^x)
  end
end

And then cleanup your query to:

import Foo
import Ecto.Query

from item in "items",
  where: date_trunc("hour", item.inserted_at) < date_trunc("hour", now()),
  select: %{regr: regr_sxy(item.y, item.x)}

However there is still a lot of repetition in your new fancy helper module. You need to repeat function name twice, name each argument, insert all that carets and stuff.

What about little help?

defmodule Foo do
  import Ecto.Function

  defqueryfunc date_trunc(part, field)
  defqueryfunc now
  defqueryfunc regr_sxy/2
end

Much cleaner…

Reasoning

Your DB is powerful. Really. A lot of computations can be done there. There is whole chapter dedicated to describing all PostgreSQL functions and Ecto supports only few of them:

• sum
• avg
• min
• max

To be exact. Saying that we have "limited choice" would be disrespectful to DB systems like PostgreSQL or Oracle. Of course Ecto core team have walid reasoning to support only that much functions: these are available in probably any DB system ever, so supporting them directly in library is no brainer. However you as end-user shouldn't be limited to so small set. Let's be honest, you probably will never change your DB engine, and if you do so, then you probably rewrite while system from the ground. So this is why this module was created. To provide you access to all functions in your SQL DB (could work with NoSQL DB also, but I test only against PostgreSQL).

For completeness you can also check Ecto.OLAP which provide helpers for some more complex functionalities like GROUPING (and in near future also window functions).

But why not introduce that directly to Ecto?

Because there is no need. Personally I would like to see Ecto splitted a little, like changesets should be in separate library in my humble opinion. Also I believe that such PR would never be merged as "non scope" for the reasons I gave earlier.