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