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