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