Thread: Character type name?? How to lower case it?
I have this script:
SELECT a.attname, t.typname, a.attlen
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'regis' and a.attname in
('var1','var2',var3');
but a.attname is of type name, inwhich text(..) or lower(..)
do not work with this type and I do not seem to be able to
convert type name to text....
Can someone help me get around this?
Thanks
--
Cheers
Douglas Nichols
dnichols@fhcrc.org
----------------------------------------------------------------------------
Fred Hutchinson Cancer Research Center
>
> I have this script:
> SELECT a.attname, t.typname, a.attlen
> FROM pg_class c, pg_attribute a, pg_type t
> WHERE c.relname = 'regis' and a.attname in
> ('var1','var2',var3');
>
> but a.attname is of type name, inwhich text(..) or lower(..)
> do not work with this type and I do not seem to be able to
> convert type name to text....
>
> Can someone help me get around this?
>
You could use case-insensitive regex operator - Your query should looks
like following:
SELECT a.attname, t.typname, a.attlen
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'regis' and (a.attname ~*'var1' OR
a.attname~*'var2' OR a.attname~*'var3');
May be this helps
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
Re: [SQL] Character type name?? How to lower case it?
From
Guido.Goldstein@t-online.de (Guido Goldstein)
Date:
Moin!
On Fri, 29 Jan 1999 09:43:33 +0100 (MET)
Remigiusz Sokolowski <rems@gdansk.sprint.pl> wrote:
> >
> > I have this script:
> > SELECT a.attname, t.typname, a.attlen
> > FROM pg_class c, pg_attribute a, pg_type t
> > WHERE c.relname = 'regis' and a.attname in
> > ('var1','var2',var3');
> >
> > but a.attname is of type name, inwhich text(..) or lower(..)
> > do not work with this type and I do not seem to be able to
> > convert type name to text....
> >
> > Can someone help me get around this?
> >
>
> You could use case-insensitive regex operator - Your query should looks
> like following:
> SELECT a.attname, t.typname, a.attlen
> FROM pg_class c, pg_attribute a, pg_type t
> WHERE c.relname = 'regis' and (a.attname ~*'var1' OR
> a.attname~*'var2' OR a.attname~*'var3');
Why not
[...]
WHERE c.relname = 'regis' and a.attname ~* 'var[1-3]'
[...]
It's a regex, remember?
The OR isn't necessary.
HIH
&
HAND
Guido
--
Monday is an awful way to spend 1/7th of your life.
> Moin!
>
> On Fri, 29 Jan 1999 09:43:33 +0100 (MET)
> Remigiusz Sokolowski <rems@gdansk.sprint.pl> wrote:
> > >
> > > I have this script:
> > > SELECT a.attname, t.typname, a.attlen
> > > FROM pg_class c, pg_attribute a, pg_type t
> > > WHERE c.relname = 'regis' and a.attname in
> > > ('var1','var2',var3');
> > >
> > > but a.attname is of type name, inwhich text(..) or lower(..)
> > > do not work with this type and I do not seem to be able to
> > > convert type name to text....
> > >
> > > Can someone help me get around this?
> > >
> >
> > You could use case-insensitive regex operator - Your query should looks
> > like following:
> > SELECT a.attname, t.typname, a.attlen
> > FROM pg_class c, pg_attribute a, pg_type t
> > WHERE c.relname = 'regis' and (a.attname ~*'var1' OR
> > a.attname~*'var2' OR a.attname~*'var3');
>
> Why not
> [...]
> WHERE c.relname = 'regis' and a.attname ~* 'var[1-3]'
> [...]
>
> It's a regex, remember?
> The OR isn't necessary.
Right, but I suppose, that var1 and var2 and var3 are only examples - it
could be that it would be 'chicks', 'flames' and 'tails' - what about
this? :-)
rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------
Actually I was talking about several fields, but to make it worst I really
do not know how many fields. So I was making a quoted set of strings to
put together at sql time to say: is filed1 in (...) how ever long (...) is
An example wuld be:
a.attname in ('hello','goodbye','baisc','perl')
but I do not want to have to know the case a.attname although I can
manipulate (..) to either.
Cheers
Douglas Nichols dnichols@fhcrc.org
----------------------------------------------------------------------------
Database Manager National Wilms Tumor Study Group
Fred Hutchinson Cancer Research Center
On Fri, 29 Jan 1999, Remigiusz Sokolowski wrote:
> > Moin!
> >
> > On Fri, 29 Jan 1999 09:43:33 +0100 (MET)
> > Remigiusz Sokolowski <rems@gdansk.sprint.pl> wrote:
> > > >
> > > > I have this script:
> > > > SELECT a.attname, t.typname, a.attlen
> > > > FROM pg_class c, pg_attribute a, pg_type t
> > > > WHERE c.relname = 'regis' and a.attname in
> > > > ('var1','var2',var3');
> > > >
> > > > but a.attname is of type name, inwhich text(..) or lower(..)
> > > > do not work with this type and I do not seem to be able to
> > > > convert type name to text....
> > > >
> > > > Can someone help me get around this?
> > > >
> > >
> > > You could use case-insensitive regex operator - Your query should looks
> > > like following:
> > > SELECT a.attname, t.typname, a.attlen
> > > FROM pg_class c, pg_attribute a, pg_type t
> > > WHERE c.relname = 'regis' and (a.attname ~*'var1' OR
> > > a.attname~*'var2' OR a.attname~*'var3');
> >
> > Why not
> > [...]
> > WHERE c.relname = 'regis' and a.attname ~* 'var[1-3]'
> > [...]
> >
> > It's a regex, remember?
> > The OR isn't necessary.
>
> Right, but I suppose, that var1 and var2 and var3 are only examples - it
> could be that it would be 'chicks', 'flames' and 'tails' - what about
> this? :-)
> rem
> -------------------------------------------------------------------*------------
> Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
> -----------------------------------------------------------------*****----------
>
>
>
> Actually I was talking about several fields, but to make it worst I really
> do not know how many fields. So I was making a quoted set of strings to
> put together at sql time to say: is filed1 in (...) how ever long (...) is
> An example wuld be:
>
> a.attname in ('hello','goodbye','baisc','perl')
>
> but I do not want to have to know the case a.attname although I can
> manipulate (..) to either.
I understand, that You have control under ('your','strings',...).
So first:
- You can just create loop for adding "OR myFld='string'"
- You can create loop for syntax with
where relname ~* 'pg_class|pg_attribute' for adding "|string" to Your
cases
Rem
-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------