Re: SELECT from multiple schemas at ones?

If you grant select to a role or public from one or more of the duplicated tables then you have a choice.

You can create a query

SELECT o1t1.col1, o1t1.col2, o2t1.col1, o2t2.col2 from owner1.table1 o1t1 owner2.table1 o2t1 WHERE ...

or you can create views

SELECT col1, col2, 'owner1table1' FROM owner1.table1 UNION ALL
SELECT col1, col2, 'owner2table1' FROM owner2.table1

etc

But maybe the best question to ask is what business problem are you trying to solve by having these (duplicated table structures with (maybe) distinct data) joined together.

If there are reporting and/or querying needs that have to be met, perhaps the best thing would be to create a new table or 2 or 3 that has these rows already stuck together.

CREATE TABLE all_the_rows AS
SELECT col1, col2, 'owner1table1' FROM owner1.table1 UNION ALL
SELECT col1, col2, 'owner2table1' FROM owner2.table1

Then you can create indexes on the merged structures etc, recreate them as needed, etc. Of course only include in the 'owner1table1' junk if it will help a business need understand where the data came from. Received on Thu Jun 10 2004 - 14:39:50 CDT