Re: Query specific table using relative position in search path
| От | George Woodring |
|---|---|
| Тема | Re: Query specific table using relative position in search path |
| Дата | |
| Msg-id | CACi+J=Sbomej2dCgrS-e0tgt8CwOrJ5hRmjh_tda3HvtBZ5FSw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Query specific table using relative position in search path (Sergey Konoplev <gray.ru@gmail.com>) |
| Ответы |
Re: Query specific table using relative position in search path
|
| Список | pgsql-sql |
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
www.iglass.net
On Fri, Nov 8, 2013 at 3:42 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
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
В списке pgsql-sql по дате отправления: