# Ecto custom SQL function

**URL:** <https://forum.elixirforum.com/t/ecto-custom-sql-function/31085>\
**Category:** Questions\
**Tags:** ecto, ecto\_sql\
**Created:** [April 29, 2020, 8:20am UTC](https://forum.elixirforum.com/t/ecto-custom-sql-function/31085 "2020-04-29T08:20:37Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![augnustin](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/augnustin/32/14538_2.png) [@augnustin](https://forum.elixirforum.com/u/augnustin)\
**Post date:** [April 29, 2020, 8:20am UTC](https://forum.elixirforum.com/t/ecto-custom-sql-function/31085/1 "2020-04-29T08:20:38Z")

</div>

Are there some Ecto gurus here?

I have this SQL function which works amazingly well (using Postgres 11.6)

```elixir
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" AS (
    SELECT unaccent("value") AS "value"
  ),
  -- lowercases the string
  "lowercase" AS (
    SELECT lower("value") AS "value"
    FROM "unaccented"
  ),
  -- remove single and double quotes
  "removed_quotes" AS (
    SELECT regexp_replace("value", '[''"]+', '', 'gi') AS "value"
    FROM "lowercase"
  ),
  -- replaces anything that's not a letter, number, hyphen('-'), or underscore('_') with a hyphen('-')
  "hyphenated" AS (
    SELECT regexp_replace("value", '[^a-z0-9\\-_]+', '-', 'gi') AS "value"
    FROM "removed_quotes"
  ),
  -- trims hyphens('-') if they exist on the head or tail of the string
  "trimmed" AS (
    SELECT regexp_replace(regexp_replace("value", '\-+$', ''), '^\-', '') AS "value"
    FROM "hyphenated"
  )
  SELECT "value" FROM "trimmed";
$$ LANGUAGE SQL STRICT IMMUTABLE;

select * from user u
where slugify(u.administrative) = 'ile-de-france';

```

I have no idea how I could insert it in a ecto query, like:

```elixir
    User
      |> where(slugify(:administrative, ^administrative))
      |> order_by([f], f.city)
      |> Repo.all()

```

Can someone help me with that? My searches for “Ecto custom SQL function” did not produce any result. ☹

Using 100% custom SQL query would be a pain because I need to chain it with other query criteria afterwards.

---

<div class="post-metadata">

**Author:** ![hauleth](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/hauleth/32/18942_2.png) [@hauleth](https://forum.elixirforum.com/u/hauleth)\
**Post date:** [April 29, 2020, 8:32am UTC](https://forum.elixirforum.com/t/ecto-custom-sql-function/31085/2 "2020-04-29T08:32:10Z")

</div>

Use `fragment` or check out my [`ecto_function`](https://github.com/hauleth/ecto_function) (which is fancy way to use `fragment`).

---

<div class="post-metadata">

**Author:** ![augnustin](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/augnustin/32/14538_2.png) [@augnustin](https://forum.elixirforum.com/u/augnustin)\
**Post date:** [April 29, 2020, 9:54am UTC](https://forum.elixirforum.com/t/ecto-custom-sql-function/31085/3 "2020-04-29T09:54:47Z")

</div>

Sounds promising @hauleth but where should I put the SQL function definition?

Your examples in the [readme.md](https://github.com/hauleth/ecto_function/blob/master/README.md) only use pre-defined SQL functions…

Thanks

---

<div class="post-metadata">

**Author:** ![hauleth](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/hauleth/32/18942_2.png) [@hauleth](https://forum.elixirforum.com/u/hauleth)\
**Post date:** [April 29, 2020, 10:16am UTC](https://forum.elixirforum.com/t/ecto-custom-sql-function/31085/4 "2020-04-29T10:16:04Z")

</div>

From the viewpoint of Ecto/EctoFunction there is no difference between these two, so in you case it should work like that:

```elixir
defqueryfunc slugify(value)

from u un user,
  where: slugify(u.administrative) == ^administrative

```

---

<div class="post-metadata">

**Author:** ![augnustin](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/augnustin/32/14538_2.png) [@augnustin](https://forum.elixirforum.com/u/augnustin)\
**Post date:** [April 29, 2020, 10:18am UTC](https://forum.elixirforum.com/t/ecto-custom-sql-function/31085/5 "2020-04-29T10:18:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hauleth](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/hauleth/32/18942_2.png) [@hauleth](https://forum.elixirforum.com/u/hauleth)\
**Post date:** [April 29, 2020, 11:33am UTC](https://forum.elixirforum.com/t/ecto-custom-sql-function/31085/6 "2020-04-29T11:33:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![augnustin](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/augnustin/32/14538_2.png) [@augnustin](https://forum.elixirforum.com/u/augnustin)\
**Post date:** [April 29, 2020, 11:44am UTC](https://forum.elixirforum.com/t/ecto-custom-sql-function/31085/7 "2020-04-29T11:44:21Z")

</div>

You rock my world @hauleth 😄

Here’s my migration:

```elixir
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.Repo, """

CREATE OR REPLACE FUNCTION slugify("value" TEXT)
RETURNS TEXT AS $$
  -- removes accents (diacritic signs) from a given string --
  WITH "unaccented" AS (
    SELECT unaccent("value") AS "value"
  ),
  -- lowercases the string
  "lowercase" AS (
    SELECT lower("value") AS "value"
    FROM "unaccented"
  ),
  -- remove single and double quotes
  "removed_quotes" AS (
    SELECT regexp_replace("value", '[''"]+', '', 'gi') AS "value"
    FROM "lowercase"
  ),
  -- replaces anything that's not a letter, number, hyphen('-'), or underscore('_') with a hyphen('-')
  "hyphenated" AS (
    SELECT regexp_replace("value", '[^a-z0-9\\-_]+', '-', 'gi') AS "value"
    FROM "removed_quotes"
  ),
  -- trims hyphens('-') if they exist on the head or tail of the string
  "trimmed" AS (
    SELECT regexp_replace(regexp_replace("value", '\-+$', ''), '^\-', '') AS "value"
    FROM "hyphenated"
  )
  SELECT "value" FROM "trimmed";
$$ LANGUAGE SQL STRICT IMMUTABLE;

    """ )
  end
end

```

---

<div class="post-metadata">

**Author:** ![hauleth](https://forum.elixirforum.com/user_avatar/forum.elixirforum.com/hauleth/32/18942_2.png) [@hauleth](https://forum.elixirforum.com/u/hauleth)\
**Post date:** [April 29, 2020, 11:49am UTC](https://forum.elixirforum.com/t/ecto-custom-sql-function/31085/8 "2020-04-29T11:49:41Z")

</div>

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}`](https://hexdocs.pm/ecto_sql/3.4.3/Ecto.Migration.html#execute/1).