From: | "Bernardo Pons" <bernardo(at)atlas-iap(dot)es> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Extracting metadata about attributes from catalog |
Date: | 2001-06-22 21:07:09 |
Message-ID: | LOBBIBBGKNPMBFIKNEGGEEHHCCAA.bernardo@atlas-iap.es |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2001-06-22 21:15:55 | Re: Multiple Indexing, performance impact |
Previous Message | Bruce Momjian | 2001-06-22 20:58:01 | Re: Good name for new lock type for VACUUM? |