Partitioning across schemas & dynamic unions

[jhmartin]

Hello, I have an application that has its data partitioned across schemas. In that I mean each schema has identically named tables / columns, but the data in each is unique.

A second application, that is not aware of this setup, needs to be able to query these tables as if they were one large table. One approach would be to make a view that performs the necessary (somewhat complex) query against each schema and UNIONs it to the rest. The catch is that the list of schemas changes periodically.

What methods are there to manage this? Having the owner of the application add on a new UNION block every time a schema is added is not very supportable. Writing a stored procedure to create the view is more reliable, but the owner is not too excited about having run it whenever a change is made. A third is to write a pipelined table function that reads the list of schemas from a configuration file (ie an external table), execute the appropriate query via dynamic SQL and pipeline it back. This depends on the config file being maintained in sync with the database, but editing text files fits in with the owners procedures better.

Are there any other ways this could be approached?