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.

Another solution would be to create a view based on a function and pass the schema name to the function.

But,  I think you have to rethink your server configuration and may be whole development environment. One server for  live data and staging, testing , training ... I found that really dangerous

Regards

________________________________
From: Alban Hertroys <haramrae@gmail.com>
To: Adam Mackler <adammackler@gmail.com>
Cc: pgsql-general@postgresql.org
Sent: Monday, September 17, 2012 4:46 PM
Subject: Re: [GENERAL] Can a view use a schema search_path?

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general