In my multi-tenant postgres database, I have a schema per tenant for my data warehouse with the same tables in each. I wish to create views and queries for these tables. If I were programming I would practice DRY and structure my code so that all of these were parameterised by the tenant. How do I achieve a similar structure in postgres?

CodePudding user response：

You can run the same queries on different schemas just by changing search_path variable.

set search_path = schema1;
select * from orders; -- resolves to schema1.orders

set search_path = schema2;
select * from orders; -- resolves to schema2.orders

As for views, you will have create/drop them individually across all schemas just like you do with the tables.