I have a database with multiple schemas all with the same structure (but of course different data...). I want to create a view that will be created in a shared schema, and when executed will be executed against the current schema. Whenever I try it, it seems the view is linked to a specific schema used when creating it, and doesn't reevaluates based on the current schema. Here is the pseudo structure/code: schema1: ======= create table t1 ... schema2: ======= create table t1 ... shared_schema: ============ create table t3 ... create the view: =========== set search_path to shared_schema, schema1; create view view1 as select * from t1; try the view: ======== set search_path to shared_schema, schema1; select * from view1; set search_path to shared_schema, schema2; select * from view1; Results: ====== In the above, both select * from view1; will return the same data, though the search path changed. Is there a way to make the view use the current search_path? -- Family management on rails: http://www.famundo.com - coming soon! My development related blog: http://devblog.famundo.com
