Ecto and postgres triggers -- Any resources for how to pull this off?

Long story short, I have a real world need to create a view for hooking up a shipping terminal to live data, but because of some early architecture decisions, using a view isn’t practical in this case. (The shipping software doesn’t do joins, only single table access, hence a view.)

I populated a lookup table with the data the shipping software needs, but if a customer changes their shipping information on their account, an update trigger to also update the associated lookup table seems smart. I have a solution that updates the data, but I would like the database to deal with itself rather than bolting logic onto the application.

Understanding that this isn’t a terribly common problem to solve, I’m not finding a lot of resources on how to deal with triggers with Ecto. The Little Ecto Cookbook, for instance, doesn’t seem to reference this sort of thing.

Anyone know of a good place to start with triggers and Ecto?

Besides migrations basically nothing of ecto cares or need to care about triggers. As for migrations there’s no ecto specific API for triggers so your only option is to use execute/2 to run raw sql. Given that context it makes sense that there’s not much documentation, as there’s actually nothing ecto specific to document in the first place. Take whatever resource you can find about the sql side of things and you should be able to put the sql into a migration to execute.

2 Likes

I should have been more clear in my explanation. I was looking at it from the migration end, so execute/2 to define the trigger is likely the way to go. This is precisely what I was looking for. Thanks!

1 Like