pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: AW: Table Attribute Help
2+ messages / 2 participants
[nested] [flat]

* Re: AW: Table Attribute Help
@ 2000-10-09 15:38  Brian C. Doyle <bcdoyle@mindspring.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Brian C. Doyle @ 2000-10-09 15:38 UTC (permalink / raw)
  To: Ingo Jung <Ingo.Jung@syscom-work.de>; +Cc: pgsql-sql

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?




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: AW: Table Attribute Help
@ 2000-10-10 17:21  Michael Fork <mfork@toledolink.com>
  parent: Brian C. Doyle <bcdoyle@mindspring.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Michael Fork @ 2000-10-10 17:21 UTC (permalink / raw)
  To: Brian C. Doyle <bcdoyle@mindspring.com>; +Cc: Ingo Jung <Ingo.Jung@syscom-work.de>; 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 |        -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: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?
> 




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2000-10-10 17:21 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-09 15:38 Re: AW: Table Attribute Help Brian C. Doyle <bcdoyle@mindspring.com>
2000-10-10 17:21 ` Michael Fork <mfork@toledolink.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox