Getting lengths of variable fields
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
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/
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
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
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
Join attrelid against the OID column of pg_class ...
regards, tom lane
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
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
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
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 |
relaclYup, 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.
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