To easy use postgres triggers woth ecto I created 2 files:

• First to create the function I want to trigger
• Second to create the trigger

defmodule Workpermit.Repo.Migrations.NextPermitNumberFunction do
  use Ecto.Migration

  def up do
    execute """
    CREATE OR REPLACE FUNCTION next_permit()
    RETURNS TRIGGER AS $category_number$ 
      BEGIN 
        SELECT coalesce(max(number), 0)+1 INTO NEW.number 
          FROM permits WHERE category = NEW.category;   
        RETURN NEW; 
      END; 
    $category_number$ LANGUAGE plpgsql;
    """
  end

  def down do
    execute "DROP FUNCTION IF EXISTS next_permit() CASCADE;"
  end
end

defmodule Workpermit.Repo.Migrations.TriggerNextPermitNumber do
  use Ecto.Migration

  def up do
    execute "CREATE TRIGGER update_next_permit_number 
    BEFORE INSERT ON PERMITS
    FOR EACH ROW EXECUTE PROCEDURE next_permit();"
  end

  def down do
    execute "DROP TRIGGER IF EXISTS update_next_permit_number ON permits;"
  end
end