Re: Query specific table using relative position in search path

On Fri, Nov 8, 2013 at 8:51 AM, George Woodring
<george.woodring@iglass.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.ru@gmail.com