Re: Open 7.3 items

On Thu, 2002-08-08 at 03:27, Marc G. Fournier wrote:
> On Wed, 7 Aug 2002, Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > OK, here is the request for vote.  Do we want:
> > >
> > > > 	1)  the old secondary passwords re-added
> > > > 	2)  the new prefixing of the database name to the username when enabled
> > > > 	3)  do nothing
> > >
> > > I vote for 2b), username@database ...
> >
> > Yes, the format was going to be my second vote, dbname.username or
> > username@dbname.  Guess I will not need that vote.  ;-)
> 
> Actually, I kinda like dbname.username myself ... it means that wne you do
> a SELECT of the pg_shadow file, it can be sorted in a useful manner (ie.
> grouped by database)

use a view :

create view pg_shadow_with_domain as
    select
        usename as fullname,
        case when (strpos(usename,'@') > 0)
             then substr(usename,1,strpos(usename,'@')-1)
             else usename
              end as usename,
        case when (strpos(usename,'@') > 0)
             then substr(usename,strpos(usename,'@')+1)
             else ''
              end as usedomain,
        usesysid,
        usecreatedb,
        usetrace,
        usesuper,
        usecatupd,
        passwd,
        valuntil
    from pg_shadow;

and sort as you wish ;)

For example, to get all bruces in all domains starting with an 'acc'
just do

select *
  from pg_shadow_with_domain 
 where usename = 'bruce'
   and domain like 'acc%' ;

------------------
Hannu