Thread: Schema search_path and views

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

On 11/6/06, Just Someone <just.some@gmail.com> wrote:
> 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.

no, or not exactly.  views resolve the search path when they are
generated.  this is a fundemental part of how they work.  functions,
however, are a bit different.  the plans are lazily generated and
'stick' to the tables that are resolved in the search path when the
plan is generated, which is basically the first time you run them in a
session.  so, you could in theory do what you want with a view if it
called functions for all the switchable parts.

merlin

Cool!

That explains it fully. So i guess there will be a better performance
to the pre-generated views at the price of more views.

Thanks!

On 11/6/06, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 11/6/06, Just Someone <just.some@gmail.com> wrote:
> > 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.
>
> no, or not exactly.  views resolve the search path when they are
> generated.  this is a fundemental part of how they work.  functions,
> however, are a bit different.  the plans are lazily generated and
> 'stick' to the tables that are resolved in the search path when the
> plan is generated, which is basically the first time you run them in a
> session.  so, you could in theory do what you want with a view if it
> called functions for all the switchable parts.
>
> merlin
>


--
Family management on rails: http://www.famundo.com - coming soon!
My development related blog: http://devblog.famundo.com