AE
Ash Elixir•3y ago
ZachDaniel

Custom Statements

Hi I have a question How do I do the following 
execute "CREATE EXTENSION IF NOT EXISTS btree_gist;"

execute """
  ALTER TABLE appointments
  ADD CONSTRAINT overlapping_appointments
  EXCLUDE USING GIST (
    doctor_id WITH =, 
    tsrange("from", "until", '[)') WITH &&
  ) WHERE (NOT canceled);
  """
end
execute "CREATE EXTENSION IF NOT EXISTS btree_gist;"

execute """
  ALTER TABLE appointments
  ADD CONSTRAINT overlapping_appointments
  EXCLUDE USING GIST (
    doctor_id WITH =, 
    tsrange("from", "until", '[)') WITH &&
  ) WHERE (NOT canceled);
  """
end
I tried to check the custom statements , it seems I am missing some import perhaps Ash.DataLayer.Info This message was copied from the ash-hq channel by user @sriky27
2 Replies
ZachDaniel
ZachDanielOP•3y ago
So you can use custom_statements for this, but you need to include a "down" for that migration. For example: 
custom_statements do
  statement :overlapping_appointments, """
  ALTER TABLE appointments
  ADD CONSTRAINT overlapping_appointments
  EXCLUDE USING GIST (
    doctor_id WITH =, 
    tsrange("from", "until", '[)') WITH &&
  ) WHERE (NOT canceled);
  """,
  """
  ALTER TABLE appointments
  DROP CONSTRAINT overlapping_appointments
  """
end
custom_statements do
  statement :overlapping_appointments, """
  ALTER TABLE appointments
  ADD CONSTRAINT overlapping_appointments
  EXCLUDE USING GIST (
    doctor_id WITH =, 
    tsrange("from", "until", '[)') WITH &&
  ) WHERE (NOT canceled);
  """,
  """
  ALTER TABLE appointments
  DROP CONSTRAINT overlapping_appointments
  """
end
And for the extension, you can get a migration automatically generated for it by adding it to your repo's installed_extensions/0 callback 
#in your repo
def installed_extensions do
  ["btree_gist"]
end
#in your repo
def installed_extensions do
  ["btree_gist"]
end
sriky27
sriky27•3y ago
Few fixes I had to do for making it compile custom_statements do statement :overlapping_appointments do up """ ALTER TABLE appointments ADD CONSTRAINT overlapping_appointments EXCLUDE USING GIST ( doctor_id WITH =, tsrange("from", "until", '[)') WITH && ) WHERE (NOT canceled); """ down """ ALTER TABLE appointments DROP CONSTRAINT overlapping_appointments """ end end Thanks @Zach Daniel for the quick suggestion.

Did you find this page helpful?