Re: Different views with same name for different users

Holger Krug wrote:

> On Wed, Jan 16, 2002 at 02:21:39PM +0100, Harald Massa wrote:
>
>>i've got a table of around 10.000 records.
>>
>>Users A, B, C are allowed to see all the records
>>
>>user D only some
>>user E only some others
>>
>>Which is the most elegant way to do this?
>>(is there any way at all????)
>>
>
> You cannot create different views with the same name in one PostgreSQL
> database.  Hence the first answer is: there is no way.
>
> Nevertheless that's not the right answer. You can create only one view:
>
>    CREATE VIEW PERS as SELECT * FROM TOTALDATABASE where (criteria)
>
> Within criteria you can check the user (`current_user') and return
> `true' or `false' depending on the record at hand and the current
> user.
>
>

how about

create view pers as
select ... from totaldb where userid='D' and (d criteria)
union
select ... from totaldb where userid in 'A, B, C' and (abc criteria)
union
select ... from totaldb where userid = 'E' and (e criteria);

You would have to match the columns, perhaps put "not available" where
appropriate.  Of course there are spiffier ways to group permissions
instead of hardcoding ids.

elein

--
--------------------------------------------------------
elein@nextbus.com
(510)420-3120
www.nextbus.com
    spinning to infinity, hallelujah
--------------------------------------------------------