Postgres Pro
Downloads
Home   >   mailing lists

Thread: fieldwidths

fieldwidths

From
ahoward
Date:

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

Re: fieldwidths

From
"Ed L."
Date:
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

Re: fieldwidths

From
ahoward
Date:
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
  ====================================

Re: fieldwidths

From
Tom Lane
Date:
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