
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



Fri, 22 Jun 2001 23:07:09 +0200
"Bernardo Pons" <bernardo@atlas-iap.es>
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:

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


Fri, 22 Jun 2001 17:45:50 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: Extracting metadata about attributes from catalog
"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

Fri, 22 Jun 2001 17:58:39 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Extracting metadata about attributes from catalog 

> 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


Sun, 24 Jun 2001 12:05:12 +0200
"Bernardo Pons" <bernardo@atlas-iap.es>
RE: Extracting metadata about attributes from catalog

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


Sun, 24 Jun 2001 12:05:13 +0200
"Bernardo Pons" <bernardo@atlas-iap.es>
RE: Extracting metadata about attributes from catalog 
"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

Sun, 24 Jun 2001 10:59:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Extracting metadata about attributes from catalog 
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.....


Sun, 24 Jun 2001 11:08:01 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
RE: Extracting metadata about attributes from catalog