pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Character type name?? How to lower case it?
7+ messages / 3 participants
[nested] [flat]

* Character type name?? How to lower case it?
@ 1999-01-28 20:56  Douglas Nichols <dnichols@fhcrc.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Douglas Nichols @ 1999-01-28 20:56 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Character type name?? How to lower case it?
@ 1999-01-29 08:43  Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  parent: Douglas Nichols <dnichols@fhcrc.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Remigiusz Sokolowski @ 1999-01-29 08:43 UTC (permalink / raw)
  To: Douglas Nichols <dnichols@fhcrc.org>; +Cc: pgsql-sql

> 
> 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           * *		
-----------------------------------------------------------------*****----------




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Character type name?? How to lower case it?
@ 1999-01-29 11:42  Guido Goldstein <Guido.Goldstein@t-online.de>
  parent: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  0 siblings, 1 reply; 7+ messages in thread

From: Guido.Goldstein@t-online.de @ 1999-01-29 11:42 UTC (permalink / raw)
  To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; +Cc: Douglas Nichols <dnichols@fhcrc.org>; pgsql-sql


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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Character type name?? How to lower case it?
@ 1999-01-29 13:51  Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  parent: Guido Goldstein <Guido.Goldstein@t-online.de>
  0 siblings, 1 reply; 7+ messages in thread

From: Remigiusz Sokolowski @ 1999-01-29 13:51 UTC (permalink / raw)
  To: Guido Goldstein <Guido.Goldstein@t-online.de>; +Cc: Douglas Nichols <dnichols@fhcrc.org>; pgsql-sql

> 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           * *		
-----------------------------------------------------------------*****----------




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Character type name?? How to lower case it?
@ 1999-01-29 19:45  Douglas Nichols <dnichols@fhcrc.org>
  parent: Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  0 siblings, 1 reply; 7+ messages in thread

From: Douglas Nichols @ 1999-01-29 19:45 UTC (permalink / raw)
  To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>; +Cc: Guido Goldstein <Guido.Goldstein@t-online.de>; pgsql-sql

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           * *		
> -----------------------------------------------------------------*****----------
> 
> 




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Character type name?? How to lower case it? (fwd)
@ 1999-02-01 07:40  Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  0 siblings, 0 replies; 7+ messages in thread

From: Remigiusz Sokolowski @ 1999-02-01 07:40 UTC (permalink / raw)
  To: pgsql-sql


> 
> Moin!   (* ignore, it's a german greeting phrase *)
> 
> Hi!
> 
> On Fri, 29 Jan 1999 14:51:29 +0100 (MET)
> 	Remigiusz Sokolowski <rems@gdansk.sprint.pl> wrote:
> [snipped]
> > 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? :-)
> 
> You're right.
> But I wouldn't make it into different OR clauses. If the select joins
> tables, I guess the ORs will decrease the performance due to multiple
> evaluations of the join.
> 
> This could be circumvented by putting the patterns in the regex.
> 
> Example:
>  where (relname ~* 'pg_class') OR (relname ~* 'pg_attribute')
> will be converted to:
>  where relname ~* 'pg_class|pg_attribute'
> 
> So, only one regex-match ist performed and no difficulties with
> multiple OR clauses will arise.
> 
> Comments?
> 

This, what I here write is only my supposition. I'm not aware of how
Postgres optimizes its queries, but I have some expierience with oyher
databases - AFAIK usually optimizer makes optimization for simple
construction of queries - for example with OR or AND clauses - in cases
much more complicated structures it could be just slower.
I want here only present my point of view - in this case I could be wrong,
cause have not too much time used postgres...
	Rem
p.s.
may be somebody more expierienced give me a hint, where to find something
about optimization of queries in Postgres?

-------------------------------------------------------------------*------------
Remigiusz Sokolowski      e-mail: rems@gdansk.sprint.pl           * *		
-----------------------------------------------------------------*****----------





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Character type name?? How to lower case it?
@ 1999-02-01 08:10  Remigiusz Sokolowski <rems@gdansk.sprint.pl>
  parent: Douglas Nichols <dnichols@fhcrc.org>
  0 siblings, 0 replies; 7+ messages in thread

From: Remigiusz Sokolowski @ 1999-02-01 08:10 UTC (permalink / raw)
  To: Douglas Nichols <dnichols@fhcrc.org>; +Cc: Guido Goldstein <Guido.Goldstein@t-online.de>; pgsql-sql

> 
> 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           * *		
-----------------------------------------------------------------*****----------





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~1999-02-01 08:10 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-28 20:56 Character type name?? How to lower case it? Douglas Nichols <dnichols@fhcrc.org>
1999-01-29 08:43 ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-01-29 11:42   ` Guido Goldstein <Guido.Goldstein@t-online.de>
1999-01-29 13:51     ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-01-29 19:45       ` Douglas Nichols <dnichols@fhcrc.org>
1999-02-01 08:10         ` Remigiusz Sokolowski <rems@gdansk.sprint.pl>
1999-02-01 07:40 Re: [SQL] Character type name?? How to lower case it? (fwd) Remigiusz Sokolowski <rems@gdansk.sprint.pl>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox