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