> -----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.