Re: AW: Table Attribute Help

I have been trying to do exactly that, with this being the closest I can
come:

football=# SELECT a.attname, t.typname, a.attlen, a.atttypmod FROM
pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'tblplayer' AND
a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY
a.attnum;   attname    | typname | attlen | atttypmod 
---------------+---------+--------+-----------play_id       | int4    |      4 |        -1play_name     | varchar |
-1|        34play_username | varchar |     -1 |        20play_password | varchar |     -1 |        20play_online   |
bool   |      1 |        -1
 

I assume that attlen is the length in bytes of the field, with -1 being
variable length.  Those who have a variable length have their length + 4
in the atttypmod field.  So here is the query I used and its output for
this type of result (its a biggie):
number |   attribute   |    type     |                        modifier                         
--------+---------------+-------------+--------------------------------     1 | play_id       | int4        | not null
defaultnextval('tb...     2 | play_name     | varchar(30) | not null      3 | play_username | varchar(16) | not null
 4 | play_password | varchar(16) | not null      5 | play_online   | bool        | default 'f'
 

----------------------------------------------------------------

SELECT      a.attnum as number,  a.attname as attribute, CASE WHEN t.typname = 'varchar' THEN     t.typname || '(' ||
a.atttypmod- 4 || ')'  ELSE     t.typname  END as type, CASE WHEN a.attnotnull = 't' THEN     'not null '::text ELSE
''::text END || 'default ' ||  CASE WHEN a.atthasdef = 't' THEN     substring(d.adsrc for 128)::text  ELSE ''::text END
asmodifier
 
FROM       pg_class c,  pg_attribute a,  pg_type t, pg_attrdef d
WHERE      c.relname = '<<TABLE NAME>>' AND  a.attnum > 0 AND  a.attrelid = c.oid AND  a.atttypid = t.oid AND c.oid =
d.adrelidAND d.adnum = a.attnum
 
UNION ALL 
SELECT      a.attnum as number,  a.attname as attribute,  CASE WHEN t.typname = 'varchar' THEN     t.typname || '(' ||
a.atttypmod- 4 || ')'  ELSE      t.typname  END as type, CASE WHEN a.attnotnull = 't' THEN     'not null '::text  ELSE
  ''::text  END as modifier
 
FROM       pg_class c,  pg_attribute a,  pg_type t
WHERE      c.relname = '<<TABLE NAME>>' AND  a.attnum > 0 AND  a.attrelid = c.oid AND  a.atttypid = t.oid AND a.attname
NOTIN (SELECT a.attname            FROM pg_class c,             pg_attribute a,             pg_attrdef d
WHEREc.relname = '<<TABLE NAME>>' AND              a.attnum > 0 AND              a.attrelid = c.oid AND
a.atttypid= t.oid AND              c.oid = d.adrelid AND              d.adnum = a.attnum)
 
ORDER BY a.attnum;

-----------------------------------------------------------------

Michael Fork - CCNA - MCP - A+ 
Network Support - Toledo Internet Access - Toledo Ohio

On Mon, 9 Oct 2000, Brian C. Doyle wrote:

> That is great thank you.
> 
> How would I grab the attribute type for an attribute with it so that the 
> out put would look like
> 
>    attname       atttype
> --------------   ----------
>   userid varchar(30)
> 
> I know that is not correct but is it possible to get that out put
> 
> 
> At 05:27 PM 10/9/00 +0200, you wrote:
> >yes it's possible,
> >
> >SELECT pg_attribute.attname
> >FROM pg_class, pg_attribute
> >WHERE
> >pg_class.relname = 'xxx' and pg_attribute.attrelid = pg_class.oid
> >
> >and pg_attribute.attnum>=1 order by pg_attribute.attnum;
> >
> >xxx is your tablename!
> >
> >
> >-----Urspr�ngliche Nachricht-----
> >Von: Brian C. Doyle [mailto:bcdoyle@mindspring.com]
> >Gesendet: Montag, 9. Oktober 2000 17:21
> >An: pgsql-sql@postgresql.org
> >Betreff: [SQL] Table Attribute Help
> >
> >
> >Hello all,
> >
> >I am trying to find a query to retrive the attributes of a table as in \d
> >tablename but as a select command.  Is this possible?
>