Thread: schema, view and role

I've this view:

create or replace view catalog_promosimple_v as
 select p.PromoSimpleID,
  p.IsPromo, p.Percent, p.OnListPrice, p.Vendible,
  p.OnStock, p.Dist, p.PromoStart, p.PromoEnd, pi.ItemID, pi.Discount
  from catalog_promosimple p
   join catalog_promosimpleitem pi on
    pi.PromoSimpleID=p.PromoSimpleID
 where p.PromoStart<now() and p.PromoEnd>=now() and p.IsPromo=true;

and I've 3 role, one is just a group and the others are an actual
user with their corresponding schema.

The table mentioned in the view actually belong to the user's schema.
They don't exist in public.
The owner of everything is the group role.

group_role no schema
user1_role (member of group_role) -> user1_role schema
user2_role (member of group_role) -> user2_role schema

Tables in user1_role schema contain some record.
Tables in user2_role contain no record.

I create the above view connected as the group_role.
I get no error message. (?)
If I select * from catalog_promosimple_v I get no error and no
record, no matter with which user I'm logged in.

If I create the view with one of the userN_role I get the expected
result.

Why creating the view from group_role doesn't report any error?
After all group_role shouldn't have in the search path userN_role
schema... and in fact it just has:

show search_path ;
  search_path
----------------
 "$user",public

If the view is just an alias for the SQL as "text" and it is not
interpreted at creation time... once I log in with one of the
userN_role I should see the correct result. If it gets interpreted
when I create the view it should report an error since those tables
don't exist in the public or group_role schema.


--
Ivan Sergio Borgonovo
http://www.webthatworks.it

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device