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(at)candle(dot)pha(dot)pa(dot)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(at)database ...
> >
> > Yes, the format was going to be my second vote, dbname.username or
> > username(at)dbname(dot) 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