I've got a postgres database which relies on the use of foreign tables to support the loading of large data sets based on user-provided (system sanitized) tables. I'd use
COPY, but the tables loaded on a recurring (but non-daily) cycle and have complex load parameters which we've built into functions (using a combination of
TRUNCATE AND INSERT,
INSERT, ON CONFLICT)
The process works well, and is stable. While I'm generally happy with it, I realize that it opens up the possibility that at some point the file paths specified in the
CREATE FOREIGN TABLE command will change (e.g. from
My question: Is there a way to point the base of the file path to a non-static value to abstract that change? Or would I be better off writing a script to collect the list of foreign tables, drop them, and rebuild the DDL with new filepath arguments?