Re: Extracting metadata about attributes from catalog - Mailing list pgsql-hackers

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
> 
>