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