Extracting metadata about attributes from catalog

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

Responses

Browse pgsql-hackers by date

  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?