Обсуждение: fieldwidths
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. i'd like to be able to do something like : select field_widths(*) from relation; where 'field_width' means the width as printed out by the default to_char() method. obviously psql does something internally to do it's formatting, but whatever it does is very fast... any ideas? -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 ====================================
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 ? Ed
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 ====================================
ahoward <ahoward@fsl.noaa.gov> writes: > 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. There is no general-purpose method for doing that. > obviously psql does something internally to do it's formatting, but whatever > it does is very fast... any ideas? psql runs through the actual strings returned by the query and takes the max length in each column. Not very brilliant ... regards, tom lane