> unexpected N API calls when joining across my domain
I'm not sure why they would be unexpected (because it should displace some other API calls). I'll hazard a guess that you're worried about fetching the same data multiple times? If that's the case, then yes, you should materialize the data into your database.
The Wrapper itself handles pagination, but you'd also want to make sure you're using filters & limits in your SQL statements.
You're right -- it's not unexpected -- maybe more like a leaky abstraction.
I imagine you want to do something like:
select
*
from
public.users join stripe.customers
on
public.users.stripe_id = stripe.customers.id
limit 100;
1. Materialize your customers into your database (like I mention in the previous comment)
2. We build a "smart" FDW, so that it parses your query and fetches the Stripe data first, then performs the join.
3. Use a CTE:
with
customers as (
select * from stripe.customers
),
users as (
select * from public.users
)
select
*
from
users join customers
on
users.stripe_id = customers.idIs there a way to propagate changes from the foreign data source through the FDW to Postgres?
Or would it just be some kind of task polls the foreign data source pulling a delta?
There's no facility for deltas, or any other way of propagating changes.
Edit: And to answer your original question, yes you can absolutely base the materialized view on data obtained from a foreign table.
Once I heard those words mentioned, I began to imagine what sort of interaction with a foreign data source's operations log FDW might have.
Of course, and depending on the foreign data source, you could probably even expose an operations log to Postgres via a FDW by defining it as a foreign table... Effectively opening up the possibility of eventually consistent replication. Lots of data stores' operations logs are accessible data collections/tables anyways.
All of this is exciting stuff!