Thread: Extracting metadata about attributes from catalog
I make queries on catalog tables in order get metadata about table
attributes. I need this metadata in order to help me controlling the data
that users enter using html forms dynamically generated with PHP.
The problem I've found is that the attribute that stores the info about data
length (attribute atttypmod of catalog table pg_attribute) is some kind of
internal coding. For example, for an attribute varchar(100) atttypmod value
is 104; for an attribute numeric(6,0) atttypmod value is 393220.
I guess I would need some kind of function in order to get the actual lenght
for the attributes. Does this function exist? Where can I find it?
Any help will be appreciated.
--
Bernardo Pons
P.S.
For example, typical output of \d <tablename> in psql is:
Attribute | Type | Modifier
-----------------+--------------+----------CustomerId | numeric(6,0) | not nullName | varchar(100)
|Series | numeric(2,0) | not nullNumber | numeric(6,0) | not nullObjectId | numeric(6,0)
|ObjectType | numeric(3,0) |Quantity | numeric(8,2) | not nullPrice | numeric(8,2) | not null
Using a query like
SELECT a.attname, t.typname, a.atttypmod, a.attnum FROM pg_class c,
pg_attribute a, pg_type t WHERE c.relname = <tablename> AND a.attnum > 0 AND
a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;
on system catalog tables I get:
attname | typname | atttypmod | attnum
-----------------+---------+-----------+--------CustomerId | numeric | 393220 | 1Name | varchar
| 104 | 2Series | numeric | 131076 | 1Number | numeric | 393220 | 2ObjectId
| numeric | 393220 | 3ObjectType | numeric | 196612 | 4Quantity | numeric | 524294
| 7Price | numeric | 524294 | 8
Do 'psql -E ...', it will display actual queries used by psql. Your particular query is: SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = '...tablename...' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum And pg_type has all information you need. On Fri, 22 Jun 2001, Bernardo Pons wrote: > > I make queries on catalog tables in order get metadata about table > attributes. I need this metadata in order to help me controlling the data > that users enter using html forms dynamically generated with PHP. > > The problem I've found is that the attribute that stores the info about data > length (attribute atttypmod of catalog table pg_attribute) is some kind of > internal coding. For example, for an attribute varchar(100) atttypmod value > is 104; for an attribute numeric(6,0) atttypmod value is 393220. > > I guess I would need some kind of function in order to get the actual lenght > for the attributes. Does this function exist? Where can I find it? > > Any help will be appreciated. > > -- > Bernardo Pons > > > P.S. > > For example, typical output of \d <tablename> in psql is: > > Attribute | Type | Modifier > -----------------+--------------+---------- > CustomerId | numeric(6,0) | not null > Name | varchar(100) | > Series | numeric(2,0) | not null > Number | numeric(6,0) | not null > ObjectId | numeric(6,0) | > ObjectType | numeric(3,0) | > Quantity | numeric(8,2) | not null > Price | numeric(8,2) | not null > > Using a query like > > SELECT a.attname, t.typname, a.atttypmod, a.attnum FROM pg_class c, > pg_attribute a, pg_type t WHERE c.relname = <tablename> AND a.attnum > 0 AND > a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum; > > on system catalog tables I get: > > attname | typname | atttypmod | attnum > -----------------+---------+-----------+-------- > CustomerId | numeric | 393220 | 1 > Name | varchar | 104 | 2 > Series | numeric | 131076 | 1 > Number | numeric | 393220 | 2 > ObjectId | numeric | 393220 | 3 > ObjectType | numeric | 196612 | 4 > Quantity | numeric | 524294 | 7 > Price | numeric | 524294 | 8 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
"Bernardo Pons" <bernardo@atlas-iap.es> writes:
> The problem I've found is that the attribute that stores the info about data
> length (attribute atttypmod of catalog table pg_attribute) is some kind of
> internal coding. For example, for an attribute varchar(100) atttypmod value
> is 104; for an attribute numeric(6,0) atttypmod value is 393220.
Yup.
> I guess I would need some kind of function in order to get the actual lenght
> for the attributes. Does this function exist? Where can I find it?
In 7.1, "format_type(typeoid, typmod)" is what produces the type
displays seen in psql. This may or may not be exactly what you want,
but that's how the knowledge of typmod encoding is exported at the
moment.
regards, tom lane
> > I guess I would need some kind of function in order to get the > actual lenght > > for the attributes. Does this function exist? Where can I find it? > > In 7.1, "format_type(typeoid, typmod)" is what produces the type > displays seen in psql. This may or may not be exactly what you want, > but that's how the knowledge of typmod encoding is exported at the > moment. There's 957 functions in psql (output of \df). Would I be so lucky that none of these functions is the one that you suggested? :-( Is "format_type(typeoid, typmod)" an internal C language function of the Postgres backend? (please... please... say no :-) If so (I'm afraid it will be) the only way to extract the actual length of a varchar field or length of integer/fractional part of a numeric field would be implementing the same functions the backend uses in my PHP modules. Any other suggestion? Regards, -- Bernardo Pons
> Do 'psql -E ...', it will display actual queries used by psql. I already do it. At the end of my first message there was an example with exactly the query you suggested. > Your particular query is: > SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, > a.atthasdef, a.attnum > FROM pg_class c, pg_attribute a, pg_type t > WHERE c.relname = '...tablename...' > AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid > ORDER BY a.attnum > > And pg_type has all information you need. But, I'm afraid pg_type has not the information I need. Just in case I missed something you have seen I wrote down a query showing all attributes of the pg_type SELECT a.attname, t.typname, t.typowner, t.typlen, t.typprtlen, t.typbyval, t.typtype, t.typisdefined, t.typdelim, t.typrelid, t.typelem, t.typinput, t.typoutput, t.typreceive, t.typsend, t.typalign, t.typdefault, a.atttypmod, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = ..TABLENAME.. AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum; but there's neither a field showing me, for example, a value 100 for a varchar(100) field nor two fields showing value 6 and 2 for a numeric(6,2) field. Maybe I'm missing something from your answer? Regards, -- Bernardo Pons
On Sun, 24 Jun 2001, Bernardo Pons wrote: > > > Do 'psql -E ...', it will display actual queries used by psql. > > I already do it. At the end of my first message there was an example with > exactly the query you suggested. > > > Your particular query is: > > SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, > > a.atthasdef, a.attnum > > FROM pg_class c, pg_attribute a, pg_type t > > WHERE c.relname = '...tablename...' > > AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid > > ORDER BY a.attnum Sorry about that. For parameterized types (like numeric, varchar), atttypmod contains specific information. For varchar-like parameters, its length of the field+4 (54 means varchar(50), for example). For numeric paremeter (numeric(a,b)), its 327680*b+a I'm not sure if there's a better (and more documented) way to decode those numbers, though.....
"Bernardo Pons" <bernardo@atlas-iap.es> writes:
>> In 7.1, "format_type(typeoid, typmod)" is what produces the type
>> displays seen in psql. This may or may not be exactly what you want,
>> but that's how the knowledge of typmod encoding is exported at the
>> moment.
> There's 957 functions in psql (output of \df).
> Would I be so lucky that none of these functions is the one that you
> suggested? :-(
regression=# \df format_type List of functionsResult | Function | Arguments
--------+-------------+--------------text | format_type | oid, integer
(1 row)
I did say 7.1, however. What version are you using?
regards, tom lane