Re: AW: Table Attribute Help
От | Michael Fork |
---|---|
Тема | Re: AW: Table Attribute Help |
Дата | |
Msg-id | Pine.BSI.4.21.0010101240240.9317-100000@glass.toledolink.com обсуждение исходный текст |
Ответ на | Re: AW: Table Attribute Help ("Brian C. Doyle" <bcdoyle@mindspring.com>) |
Список | pgsql-sql |
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? >
В списке pgsql-sql по дате отправления: