Ecto custom SQL function

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}.

You rock my world @hauleth

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.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

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

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

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

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

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