Horton

Thread

  1. Getting lengths of variable fields

    Michelle Murrain <mpm@norwottuck.com> — 2001-03-05T21:44:54Z

    Hi folks,

I'm writing a very robust validation script in perl for database entry. One 
of the things I'd like to do is check how large a field is, and make sure 
that the entry into that field isn't too big. Problem is, for variable length 
fields, DBD::Pg returns a -1 size, using the pg_size attribute.

Is there something I am missing? Is there a way to get the size of variable 
length types using DBI/DBD::Pg, in particular, char() and varchar()?

Michelle
-- 
------------
Michelle Murrain, Ph.D.
President
Norwottuck Technology Resources
mpm@norwottuck.com
http://www.norwottuck.com

  2. Re: Getting lengths of variable fields

    Peter Eisentraut <peter_e@gmx.net> — 2001-03-05T22:40:57Z

    Michelle Murrain writes:

> I'm writing a very robust validation script in perl for database entry. One
> of the things I'd like to do is check how large a field is, and make sure
> that the entry into that field isn't too big. Problem is, for variable length
> fields, DBD::Pg returns a -1 size, using the pg_size attribute.
>
> Is there something I am missing? Is there a way to get the size of variable
> length types using DBI/DBD::Pg, in particular, char() and varchar()?

Normally, you'd use LENGTH or OCTET_LENGTH.  If you want to get the
storage size on disk, you could add 4 to what you get as length, but this
result seems to be of dubious value, especially with TOAST (compression,
out-of-line storage).

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

  3. Re: Getting lengths of variable fields

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-05T22:55:04Z

    Peter Eisentraut <peter_e@gmx.net> writes:
> Michelle Murrain writes:
>> Is there something I am missing? Is there a way to get the size of variable
>> length types using DBI/DBD::Pg, in particular, char() and varchar()?

> Normally, you'd use LENGTH or OCTET_LENGTH.  If you want to get the
> storage size on disk, you could add 4 to what you get as length, but this
> result seems to be of dubious value, especially with TOAST (compression,
> out-of-line storage).

What Michelle seems to want is the declared limit on field width, not
the actual width of any particular value.

This info is stored in the 'atttypmod' field of pg_attribute, but I
don't know whether DBD::Pg provides any handy interface to that.  You
might have to get down-and-dirty enough to select it directly out of
pg_attribute ...

			regards, tom lane

  4. Re: Getting lengths of variable fields

    Michelle Murrain <mpm@norwottuck.com> — 2001-03-05T23:04:20Z

    On Monday 05 March 2001 05:55 pm, Tom Lane wrote:

> What Michelle seems to want is the declared limit on field width, not
> the actual width of any particular value.

Yes, that's exactly what I want.

> This info is stored in the 'atttypmod' field of pg_attribute, but I
> don't know whether DBD::Pg provides any handy interface to that.  You
> might have to get down-and-dirty enough to select it directly out of
> pg_attribute ...

I couldn't seem to find it - and I'm going to show my naivete here - how do I 
get hold of it if there is no interface in DBD::Pg?

THanks!!

Michelle
------------
Michelle Murrain, Ph.D.
President
Norwottuck Technology Resources
mpm@norwottuck.com
http://www.norwottuck.com

  5. Re: Getting lengths of variable fields

    Boulat Khakimov <boulat@inet-interactif.com> — 2001-03-05T23:56:18Z

    Tom Lane wrote:
> 
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Michelle Murrain writes:
> >> Is there something I am missing? Is there a way to get the size of variable
> >> length types using DBI/DBD::Pg, in particular, char() and varchar()?
> 
> > Normally, you'd use LENGTH or OCTET_LENGTH.  If you want to get the
> > storage size on disk, you could add 4 to what you get as length, but this
> > result seems to be of dubious value, especially with TOAST (compression,
> > out-of-line storage).
> 
> What Michelle seems to want is the declared limit on field width, not
> the actual width of any particular value.
> 
> This info is stored in the 'atttypmod' field of pg_attribute, but I
> don't know whether DBD::Pg provides any handy interface to that.  You
> might have to get down-and-dirty enough to select it directly out of
> pg_attribute ...
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Hi Tom,

When I query the pg_attribute table to get the limit on a specific field
I get more than one row, because I use the same field name in few
tables. 
However each row has different  "attrelid", I suppose that means that
every 
table has its one unique "attrelid". My question is how do I find out
the  
"attrelid" if I only know that table name??

That's the only thing that keeps me from getting max field limit for a
specific
field in a specific table...


Regards,
Boulat Khakimov


-- 
Nothing Like the Sun

  6. Re: Getting lengths of variable fields

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-06T00:05:15Z

    Join attrelid against the OID column of pg_class ...

			regards, tom lane

  7. Re: Getting lengths of variable fields

    Michelle Murrain <mpm@norwottuck.com> — 2001-03-06T00:24:30Z

    On Monday 05 March 2001 05:55 pm, Tom Lane wrote:
>
> This info is stored in the 'atttypmod' field of pg_attribute, but I
> don't know whether DBD::Pg provides any handy interface to that.  You
> might have to get down-and-dirty enough to select it directly out of
> pg_attribute ...

Ah, I got it. Just do a select. It's a system table I hadn't learned about 
yet. And, I have to subtract 4 from it to get the real length. Thanks!!

Michelle
------------
Michelle Murrain, Ph.D.
President
Norwottuck Technology Resources
mpm@norwottuck.com
http://www.norwottuck.com

  8. Re: Getting lengths of variable fields

    Michelle Murrain <mpm@norwottuck.com> — 2001-03-06T00:34:48Z

    On Monday 05 March 2001 07:05 pm, Tom Lane wrote:
> Join attrelid against the OID column of pg_class ...

Um, which column?  When I look at that table, I see the following columns...

relname             | reltype | relowner | relam | relpages | reltuples | 
rellongrelid | relhasindex | relisshared | relkind | relnatts | relchecks | 
reltriggers | relukeys | relfkeys | relrefs | relhaspkey | relhasrules |  
relacl

Thanks again!

Michelle
------------
Michelle Murrain, Ph.D.
President
Norwottuck Technology Resources
mpm@norwottuck.com
http://www.norwottuck.com

  9. Re: Getting lengths of variable fields

    Boulat Khakimov <boulat@inet-interactif.com> — 2001-03-06T00:45:45Z

    Michelle Murrain wrote:
> 
> On Monday 05 March 2001 07:05 pm, Tom Lane wrote:
> > Join attrelid against the OID column of pg_class ...
> 
> Um, which column?  When I look at that table, I see the following columns...
> 
> relname             | reltype | relowner | relam | relpages | reltuples |
> rellongrelid | relhasindex | relisshared | relkind | relnatts | relchecks |
> reltriggers | relukeys | relfkeys | relrefs | relhaspkey | relhasrules |
> relacl
> 

Yup, indeed there is no such field in pg_class.

Tom?


--
Nothing Like the Sun

  10. Re: Getting lengths of variable fields

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-03-06T01:15:37Z

    On Mon, 5 Mar 2001, Boulat Khakimov wrote:

> Michelle Murrain wrote:
> > 
> > On Monday 05 March 2001 07:05 pm, Tom Lane wrote:
> > > Join attrelid against the OID column of pg_class ...
> > 
> > Um, which column?  When I look at that table, I see the following columns...
> > 
> > relname             | reltype | relowner | relam | relpages | reltuples |
> > rellongrelid | relhasindex | relisshared | relkind | relnatts | relchecks |
> > reltriggers | relukeys | relfkeys | relrefs | relhaspkey | relhasrules |
> > relacl
> > 
> 
> Yup, indeed there is no such field in pg_class.
 
Figure I can field this one in hopes of giving Tom more time for other
things :)

It's a system column, so it doesn't show up in the 
column list that you see from a select * query, but if you do a
select oid from pg_class; you'll get it.

  11. Re: Getting lengths of variable fields

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-06T01:24:25Z

    > > Yup, indeed there is no such field in pg_class.
>  
> Figure I can field this one in hopes of giving Tom more time for other
> things :)
> 
> It's a system column, so it doesn't show up in the 
> column list that you see from a select * query, but if you do a
> select oid from pg_class; you'll get it.

Let me use this system catalog discussion to announce the completion of
my database internals presentation at:

	http://candle.pha.pa.us/main/writings/internals.pdf

There is a system catalog diagram near the end.

Comments welcomed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026