Re: Query specific table using relative position in search path

There is only one user logging in. The web app db user.

If I have two search paths.

search_path='cust1restricted', 'cust1' and
search_path='cust2restricted','cust2'

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;

That would work for both search paths.

Thanks
Woody

iGLASS Networks
www.iglass.net

On Fri, Nov 8, 2013 at 3:42 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:

> On Fri, Nov 8, 2013 at 8:51 AM, George Woodring
> <george(dot)woodring(at)iglass(dot)net> wrote:
> > Currently we have a database per customer approach to our database
> > methodology. In this we have the customer tables in the public schema.
> We
> > create restrictions by having views in different schemas and modifying
> the
> > search path. (restriction, public).
> >
> > We are investigating having a scheme of having multiple customers in the
> > same database and each of them having their own schema ( restriction,
> > custName ).
> >
> > Is there a way to have a generic query to a custName table by referencing
> > its position in the search_path?
> >
> > Example
> > select * from mytable; -- Gives restricted answers
> > select * from super.mytable -- Gives custName answers
> >
> > Currently this is done by public.mytable, but I am curious if there is a
> > generic way without having to know the custName schema.
>
> I am not quite understand the problem, but my guess is that
>
> search_path = "$user",public
>
> might help you.
>
> It means that every user will "see" (in the meaning that you do not
> need to specify schema explicitly) all the stuff in the public schema
> and in the schema named with the user name after being logged in.
>
> --
> 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(dot)ru(at)gmail(dot)com
>