Extracting metadata about attributes from catalog

Поиск
Список
Период
Сортировка
От Bernardo Pons
Тема Extracting metadata about attributes from catalog
Дата
Msg-id LOBBIBBGKNPMBFIKNEGGEEHHCCAA.bernardo@atlas-iap.es
обсуждение исходный текст
Ответы Re: Extracting metadata about attributes from catalog  (Alex Pilosov <alex@pilosoft.com>)
Re: Extracting metadata about attributes from catalog  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 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
 




В списке pgsql-hackers по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: Good name for new lock type for VACUUM?
Следующее
От: Alex Pilosov
Дата:
Сообщение: [PATCH] by request: base64 for bytea