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(at)fsl(dot)noaa(dot)gov
| Phone: 303-497-7238
| Fax: 303-497-7259
====================================