Thread: Query specific table using relative position in search path
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.
Thanks
Woody
iGLASS Networks
www.iglass.net
www.iglass.net
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
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
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