Re: Schema search_path and views

2006/11/6, Just Someone <just(dot)some(at)gmail(dot)com>:
>
> 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;

In this point, your view was created with thi code:
CREATE VIEW "view1" AS SELECT t1.column1, t1.column2, ..., t1.columnN
FROM schema1.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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
William Leite Araújo