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
