Are there some Ecto gurus here? I have this SQL function which works amazingly well (using Postgres 11.6) CREATE EXTENSION IF NOT EXISTS "unaccent"; CREATE OR REPLACE FUNCTION slugify("value" TEXT) RETURNS TEXT AS $$ -- removes accents (diacritic signs) from a given string -- WITH "unaccented…

You need to create that function in migration. You do not “tell” Postgres about function each time you want to use it.

Sounds promising @hauleth but where should I put the SQL function definition? Your examples in the readme.md only use pre-defined SQL functions… Thanks

From the viewpoint of Ecto/EctoFunction there is no difference between these two, so in you case it should work like that: defqueryfunc slugify(value) from u un user, where: slugify(u.administrative) == ^administrative

Yes, but I do need to tell postgres about this function definition. Any idiomatic way to do that with Ecto (or postgrex)?

You rock my world @hauleth :smile: Here’s my migration: defmodule Vae.Repo.Migrations.AddSlugifySQLFunction do use Ecto.Migration def change do Ecto.Adapters.SQL.query!( MyApp.Repo, """ CREATE EXTENSION IF NOT EXISTS "unaccent"; """ ) Ecto.Adapters.SQL.query!( MyApp…

You do not need to use Ecto,Adapters.SQL.query!/2 in the migrations. More - it is dangerous to do so (for example what if you will have multiple repos?). Instead use execute/{1,2} .

Elixir Programming Language Forum

Ecto custom SQL function

Questions & Help Questions

hauleth April 29, 2020, 8:32am 2

Use fragment or check out my ecto_function (which is fancy way to use fragment).