| From: | Samuel Thoraval <samuel(dot)thoraval(at)librophyt(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | views to behave differently according to user and schema | 
| Date: | 2005-08-03 10:36:54 | 
| Message-ID: | 42F09E46.1010109@librophyt.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hi,
I'd like views to behave differently according to the user and schema.
For instance, having 3 schemas corresponding to 3 users :
-- AS USER nsp1 :
CREATE TABLE nsp1.test (id integer);
CREATE VIEW nsp2.test AS SELECT * FROM nsp1.test WHERE id <= 10;
CREATE VIEW nsp3.test AS SELECT * FROM nsp1.test WHERE id <= 50;
CREATE or REPLACE VIEW public.vtest AS SELECT * FROM test WHERE id <=5;
Here, test in VIEW vtest is actually being tied up to the first 
relation/view it found in the search path (nsp1,public) :
\d public.vtest;
     View "public.vtest"
 Column |  Type   | Modifiers
--------+---------+-----------
id | integer |
View definition:
 SELECT test.id
   FROM nsp1.test
  WHERE test.id <= 10;
Is it possible to change this behavior so that the VIEW will dynamically 
use the search_path variable (when the schema was not specifically set) ?
In this case, when user nsp2 loggs in, the VIEW vtest would be using 
VIEW nsp2.test, when user nsp3 loggs in it would use VIEW nsp3.test, for 
user nsp1 TABLE nsp1.test ...
Cheers,
-- 
Samuel Thoraval
LIBROPHYT, Bioinformatique
Centre de Cadarache
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Vahe Ghorghorian | 2005-08-03 11:30:06 | Hello | 
| Previous Message | Richard Huxton | 2005-08-03 08:43:35 | Re: Questions about anonymous procedure/function. |