Re: Query specific table using relative position in search path

On Fri, Nov 8, 2013 at 12:57 PM, George Woodring
<george.woodring@iglass.net> wrote:
> If I have two search paths.
>
> search_path='cust1restricted', 'cust1' and
> search_path='cust2restricted','cust2'

You mean that you set one of these search paths depending on some
conditions after the user logged in, right?

> I am looking for a generic query that would look into the parent schema
> without having to know the parent schema
> I know that I can do
>
> select * from cust1.table
> and
> select * from cust2.table
>
> but what I was wondering if there was a way to write the query based on the
> position in the search_path
>
> select * from {upOneLevel}.table;

There are no way to specify a "super" schema, AFAIK.

If your issue is that you need a kind of schema priority, then you can
just omit schema name in your query and you will get the data from the
the table that is in the first schema, if it exists there. If there
are no such table in the first schema then it will try to get it from
the second one.

For example, if table1 exists in both cust1restricted and cust1, then
"select * from table1" will get the data from the table that is in
cust1restricted. But if it exists in the cust1 table only, then it
will get the data from the table in cust1.

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com