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 | -1
play_name | varchar | -1 | 34
play_username | varchar | -1 | 20
play_password | varchar | -1 | 20
play_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 default nextval('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 as modifier
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.adrelid AND
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 NOT IN (SELECT a.attname
FROM pg_class c,
pg_attribute a,
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.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:[EMAIL PROTECTED]]
> >Gesendet: Montag, 9. Oktober 2000 17:21
> >An: [EMAIL PROTECTED]
> >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?
>
