Interpolate Ecto.Enum in fragment/1

I have tried this code:

def my_func(params) do
  ...
  |> select(
        [l],
        %{
          date: fragment("date_trunc('?',?) as date",
            ^params.date_trunc_type,
            l.inserted_at),
        }
      )
  ...
  |> Repo.all()
end

But i’m stacked at this error:

** (Postgrex.Error) ERROR 42P18 (indeterminate_datatype) could not determine data type of parameter $1

query: SELECT date_trunc('$1',s0."inserted_at") as date...

My Modules:

defmodule Administration.Reports.Params do
  ...

  alias Administration.Enum.DateTruncType
  embedded_schema do
    ...
    field :date_trunc_type, DateTruncType, default: :month
  end
  
  ...
end

defmodule Administration.Enum.DateTruncType do
  use EctoEnum,
    hour: "hour",
    day: "day",
    month: "month",
    year: "year"
end

I want to construct this sql’s SELECTs

SELECT date_trunc('hour', inserted_at)
SELECT date_trunc('day', inserted_at)
SELECT date_trunc('month', inserted_at)
SELECT date_trunc('year', inserted_at)

When you are using fragments Ecto doesn’t know what type you are using so it won’t convert the atom to string. You need to tell it the type using type/2. This can be either a field name or a type name.

More info: Ecto.Query.API — Ecto v3.10.3

2 Likes

Also remove the quotes around the parameter. If the parameter is a string postgres can deal with that without you putting quotes up.

2 Likes
%{
        date: fragment("date_trunc(?,?) as date",
          type(^params.date_trunc_type, DateTruncType),
          l.inserted_at)
}

This worked! I tried type/2 before like this, but this didn’t work. Mb @LostKobrakai advice helped or i’ve used type/2 wrong.

@LostKobrakai, @joey_the_snake ty for help.