Ecto migration to allow postrges to generate local ids in a SaaS app

create_postgres_procedures.exs

defmodule Repo.Migrations.CreateProcedures do @doc """
Creates procedure to get and update a local_id number on SaaS apps where
next local id is stored in the tenant table.

Example trigger is given  for the user table
"""
  use Ecto.Migration
 
  def up do
    execute "CREATE OR REPLACE FUNCTION get_local_id(tenant_id integer) RETURNS integer AS $$ 
      UPDATE tenants 
        SET next_local_id = next_local_id+1 
        WHERE id = get_local_id.tenant_id
      RETURNING next_local_id - 1;
    $$ LANGUAGE SQL;"

    execute "CREATE OR REPLACE FUNCTION add_local_id() returns TRIGGER as $add_local_id$ 
    BEGIN
      NEW.local_id = get_local_id(NEW.tenant_id);
      RETURN NEW; 
    END
    $add_local_id$ LANGUAGE plpgsql;"

    execute "CREATE TRIGGER add_user
      BEFORE INSERT ON users
      FOR EACH ROW
      EXECUTE PROCEDURE add_local_id()"
  end
end