Re: fieldwidths

On Thu, 20 Mar 2003, Ed L. wrote:

> On Thursday March 20 2003 4:01, ahoward wrote:
> > postgresql'rs-
> >
> > i've looked through to docs a bit, but have not found a way to quickly
> > calculate the maximum fieldwidth of each field in a tuple set.
>
> For varchars...
>
> select max(char_length(trim(trailing ' ' from mycolumn))) from relation
>
> or maybe
>
> select max(char_length(trim(both ' ' from mycolumn))) from relation
>
> ?

curiously, this seems to work for more than just varchars :

howardat=# select max(char_length(42)) from foo;
max
-----
2
(1 row)

howardat=# select max(char_length(42.1)) from foo;
 max
-----
   4
(1 row)


howardat=# select max(char_length(now())) from foo;
 max
-----
  29
(1 row)


suprising.  this may work.  in my code i could do something like ;

  selection = (fieldnames.map{|fieldname| "max(char_length(#{fieldname}))"}).join ' '

  sql = <<-sql
    select #{selection} from relation
  sql

etc...

but i'm unsure how to contruct this from pure sql (my sql is *weak*).

-a


--
  ====================================
  | Ara Howard
  | NOAA Forecast Systems Laboratory
  | Information and Technology Services
  | Data Systems Group
  | R/FST 325 Broadway
  | Boulder, CO 80305-3328
  | Email: ahoward@fsl.noaa.gov
  | Phone:  303-497-7238
  | Fax:    303-497-7259
  ====================================