Thread: Can a view use a schema search_path?

Adam Mackler, 17.09.2012 11:06:
> I have the feeling the answer is no, but I would like an
> authoritative answer before I give up.
>
> My plan was to have two schemas: one for the live data, and one for
> staging, training, and testing.  Both schemas would have
> identically-named tables.  I wanted to create a single view in the
> public schema, and have that one view refer to the tables in one or
> the other schema depending on my search_path setting at the time I
> query the view.
>
> But it seems that at the time the view is created it decides which
> schema's table it's referring to, even if I don't explicitly qualify
> the table names with the schema name.
>
> Am I correct in concluding that there's no way to have a single view
> in the public schema that selects data from tables in different other
> schemas depending on my search_path at the time I execute a query
> involving that view?
>

You could achieve this using a set returning function.
Inside the function you'd check the search_path and then dynamically build the approriate SELECT statement

Regards
Thomas

On Mon, Sep 17, 2012 at 7:06 PM, Adam Mackler <adammackler@gmail.com> wrote:
> Am I correct in concluding that there's no way to have a single view in the
> public schema that selects data from tables in different other schemas
> depending on my search_path at the time I execute a query involving that
> view?

I don't think so, as I've wanted something similar myself. The easiest
way I found is to create two views, in the same schemas as their
corresponding tables. It's double maintenance but it's cheap to run.

ChrisA

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Chris Angelico
> Sent: Monday, September 17, 2012 9:12 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Can a view use a schema search_path?
>
> On Mon, Sep 17, 2012 at 7:06 PM, Adam Mackler <adammackler@gmail.com>
> wrote:
> > Am I correct in concluding that there's no way to have a single view
> > in the public schema that selects data from tables in different other
> > schemas depending on my search_path at the time I execute a query
> > involving that view?
>
> I don't think so, as I've wanted something similar myself. The easiest way
I
> found is to create two views, in the same schemas as their corresponding
> tables. It's double maintenance but it's cheap to run.
>
> ChrisA
>

FWIW:

If you go down this route I would make sure that you create separate users
for testing and production and only give the necessary permissions to each.
Since you are already duplicating your entire schema for tables it makes
sense to do the same for the necessary views.  I would not solely rely on
search_path to limit the ability to cause unintentional damage by operating
on production data while thinking you are working on test data.

I can see why you would want to have one single view but you are better off
having a routine that compares the production and testing schemas for
variances instead of "overloading".  While a set returning function would
work introducing that complexity probably isn't worth the effort.

One thing I have found is that by creating a schema for only non-persisted
objects (e.g., views and functions) I can simply drop the entire schema and
recreate it from source - all without impacting any actual tables.  That way
after running your tests using the test schemas you can simply drop and
recreate the production objects from the same source as your created the
test objects.

David J.

On 17 September 2012 11:06, Adam Mackler <adammackler@gmail.com> wrote:
> I have the feeling the answer is no, but I would like an authoritative
> answer before I give up.
>
> My plan was to have two schemas: one for the live data, and one for staging,
> training, and testing.  Both schemas would have identically-named tables.  I
> wanted to create a single view in the public schema, and have that one view
> refer to the tables in one or the other schema depending on my search_path
> setting at the time I query the view.
>
> But it seems that at the time the view is created it decides which schema's
> table it's referring to, even if I don't explicitly qualify the table names
> with the schema name.
>
> Am I correct in concluding that there's no way to have a single view in the
> public schema that selects data from tables in different other schemas
> depending on my search_path at the time I execute a query involving that
> view?
>
> Thanks.
> --
> Adam Mackler

Wouldn't it be easier to create a view in each schema with the
appropriate search_path?

That way, you get the view that your search_path points to, which then
in turn uses the tables from the search_path in use when the view was
created.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.