Extracting metadata about attributes from catalog

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:

"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

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

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

"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 functions
Result | Function | Arguments
--------+-------------+--------------
text | format_type | oid, integer
(1 row)

I did say 7.1, however. What version are you using?

regards, tom lane

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