I am trying to modified a shared development database, for a dev team to use for application development/testing.
Most of the collective work (table names, views, etc) are stored in the public schema, but I have set up schemas for each user to use as scratch space. However, the real goal would be for a user to use the objects in their schema if they exist and then fall back on the other objects, like how a search_path is used today.
This would probably be better described with an example. Let's say there's a database for an automotive repair application that a team is working on that has some tables and a view:
public.automobiles public.parts public.inventory public.mechanics public.schedule public.v_repairs -- view that joins fields from all tables above
This is working great, but let's say a developer (e.g., Sally) wants to test a new feature out, with her own dataset to check for visual feedback, or threshold testing. She creates a table in her own schema,
sally.schedule. Because the default search_path is something like
"$user",public, any simple queries she creates, would first check her schema before public. For example:
SELECT * FROM schedule LEFT JOIN mechanics USING(mechanic_id);
This would use public.mechanics and sally.schedule, when sally is connected to the database. That's exactly the intended use, but when saving a view, it fully qualifies the table name by inserting the schema. So if the same query above was created as a view in the public schema it would look like:
SELECT * FROM public.schedule LEFT JOIN public.mechanics USING(mechanic_id);
The magic of search_path is negated. When Sally connects to the database to call the view (
SELECT * FROM v_mechanics_schedule), it ignores the sally.schedule table she created, and only uses the public one.
Is there a way to not have Postgres store the schema name for table/view objects when saving a view?
Note: this is something new I was looking into, but something I've never really had the need for, since developers can typically clone applications, replicate databases, and work within their own sandbox environment. No need for some clever collaborative schema setup