Hi! As I said in other messages, I'm planning with a partner of mine to use inheritance and schemas to separate access and data from several sources (companies) for a client we have (accounting office). The idea is: - 1 base schema with all table definitions plus functions plus views - 1 schema per client he (our client) have where all tables would be inherited from the base schema - Using views and functions declared on base schema to also manipulate and retrieve data from each schema (Before starting, I've made a dump available at http://godoy.homeip.net/~godoy/hydrogen.sql containing basic data for testing, if you're interested you'd only need to change some usernames for permissions in there.) But we're facing some problems and before doing anything more serious we'd like to know what to expect when: a) We are with some restricted search_path set (e.g. after "SET search_path TO schema_1") and we make a "SELECT * FROM base.view". What we're seeing is that views are tied to the schema where they were created, no matter if they are or not fully qualified in their definition. Is this correct? I'd expect views to respect the search_path if they aren't fully qualified (i.e. if I created them as "SELECT something FROM table" instead of "SELECT something FROM schema.table"). b) We are seeing a really weird behaviour when we use functions. It appears that it disregards the search_path from the second run and on. If I SELECT from a function with the search_path set to, e.g., schema_1, then when I set it to schema_2 then I'll still see data from schema_1. Note, here, that even the function being created on the base schema results were correctly retrieved at first execution. (You can repeat that use the above dump by connecting, setting the search path to any of three schemas, selecting from the function, changing to other schema and then selecting again from the same function -- you'll see the same result --; then, if you reconnect and do a first select in another schema and change your search_path you'll see a different result from the previous connection but it will be the same result for both search_paths.) Are these expected behaviors? Is there any way to make both views and functions work with search_path? It would be really great to be able to define them only once and use them on all "1k" schemas unchanged. I believe that the behavior with functions is "a bit" dangerous since it allows leaking information from one schema to another after a context change. (These tests aren't taking permissions into account yet, so after applying those this problem could be reduced but...) TIA, -- Jorge Godoy <jgodoy@xxxxxxxxx>
