On Wed, 5 Nov 2003, Jan Wieck wrote:
> Chris Bowlby wrote: > > > Hi All, > > > > I work with Marc Fournier, for those who don't know, and have been > > working extensively with the schemas feature for Hub.Org's new Account > > Management package. Each client's data is stored in a seperate schema > > set asside just for them (though they will never have direct access to > > it, it helps us "balance" out the data. Each schema has the same set of > > tables, with which different data is stored, depending on the client. > > > > I've run into two issues and I've been reading over the docs in > > relation to schemas in the hopes that I could find a solution to my > > problems. Here are the details of the issues: > > > > 1. The first issue I'm running with, is that if I have several schemas > > with the following name: > > > > public > > test_001 > > test_002 > > test_003 > > > > the test_* schemas have the same tables listed in the their name > > spaces, such that: > > > > Schema test_001 contains: > > > > test_contact; > > test_domains; > > test_accounts; > > ... > > > > Schema test_002 contains: > > > > test_contact; > > test_domains; > > test_accounts; > > ... > > > > etc. > > > > As you can see this is a nice, clean way to break down some datasets. > > But, if I do: > > > > set search_path to public, test_001, test_002; > > > > I only get access to the tables in test_001 and public, the tables in > > test_002 are not listed, and thus I do not see them on the screen while > > doing a "\d". > > > > 2. Issue 2, is more a SQL issue then anything, but I'd like to be able > > to do something to the affect of: > > > > SELECT * FROM test_*.test_domains ORDER BY domain_name; > > > > or better yet: > > > > SELECT * from public.domain_summary ds LEFT JOIN test_*.test_domains td > > ON (td.domain_id = ds.id); > > > > This would mean I can run one query to get all of the information I > > need, rather then having to run a query for each client that I have to > > list domains for. If I've got 10,000 clients, that's going to take a ton > > of time to get the results I'm looking for... > > > > Does anyone know if any of these issues have been addressed in 7.4? I > > see some references to schema based changes, but no details on what > > those changes were. If not, are there any plans to do something like > > that? > > You can't use a wildcard like that, but you can create a view like > > CREATE VIEW public.all_test_domains AS > SELECT * FROM test_001.test_domains > UNION ALL > SELECT * FROM test_002.test_domains; > > You have to change that view as customers (resp. schemas) come and go. Oh, just thought of it ... I know there used to be limitation on this, but I swore that they were removed ... how about a function? could you do something like: for i in `SELECT nspname FROM nspname WHERE nspname LIKE 'test_*'` ... I know functins returning multi-rows was a limitation in the past, but didn't that *just* get corrected, or am I mis-remembering? ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings