Would it be possible to emulate triggers?

[kiwicopple]

Foreign Tables don't support triggers. Would it be possible to emulate them in Wrappers?

See this thread for more context: https://x.com/vimtor_/status/1735244020421455906?s=20

[vimtor]

To give a real-world example, I developed a news site for a client using Supabase and Stripe. On the site, users could buy one-off premium articles to read. The way we achieved this was by listening for product purchases via Stripe webhooks and inserting a new row into articles_purchases using the metadata of the Stripe transaction. At the time, you released the first version of Supabase Wrappers, and I thought to myself, "Man, wouldn't it be cool to create a trigger for this?"

[burmecia]

Emulating trigger on foreign table would need to constantly monitor source table for changes, that is technically possible but may need time efficiency tradeoff. Actually, if we can build that source monitoring mechanism, it will bring us more possibilities. For example, when new source data detected, we can do

• insert the new record to another table, either local or foreign table
• send notification to destination
• call API endpoint
• etc.

Basically, we just build a mini CDC (change data capture) and ETL/ELT pipeline around the foreign table. That's the most common usage scenario for FDW, uses normally build it by themselves but this feature will make such task simpler.