pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
dynamic DB descriptions
2+ messages / 2 participants
[nested] [flat]

* dynamic DB descriptions
@ 2004-08-11 17:02 Darren Sunley <darrensunley@hotmail.com>
  2004-08-13 19:05 ` Re: dynamic DB descriptions Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Darren Sunley @ 2004-08-11 17:02 UTC (permalink / raw)
  To: pgsql-admin

Hi,

I was just wondering if anyone new of a way of pulling out the attribute 
names of a table on the fly.

I've managed to figure out how to pull out table names and how to pull out 
attr names from a table once I know what the attrelid is. However, I don't 
seem to be able to tie up the table name and/or attrelid with the one used 
to store the attributes. I thought that the relfilenode value might be the 
one, but it appears not.

Any help would be greatly appreciated.

Thanks,
Darren



holidayinfo=# SELECT DISTINCT relname FROM pg_stat_user_tables ORDER BY 
relname ASC;

relname
----------
airports
city
country
flight
login
map
picture
sight
tip
trip
tripcity
users
video
(13 rows)

holidayinfo=# select * from pg_class where relname='trip';

relname | reltype | relowner | relam | relfilenode | relpages | reltuples | 
reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | 
relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | 
relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
---------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
trip    |   59463 |        1 |     0 |       59462 |        1 |        67 |  
            0 |             0 | t           | f           | r       |        
2 |         0 |           0 |        0 |        0 |       0 | t          | f 
          | f           | f              |
(1 row)

holidayinfo=# select * from pg_attribute where attrelid=59462;

attrelid |     attname      | atttypid | attstattarget | attlen | attnum | 
attndims | attcacheoff | atttypmod | attbyval | attstorage | attisset | 
attalign | attnotnull | atthasdef
----------+------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+----------+------------+-----------
    59462 | tableoid         |       26 |             0 |      4 |     -7 |  
       0 |          -1 |        -1 | t        | p          | f        | i    
     | f          | f
    59462 | cmax             |       29 |             0 |      4 |     -6 |  
       0 |          -1 |        -1 | t        | p          | f        | i    
     | f          | f
    59462 | xmax             |       28 |             0 |      4 |     -5 |  
       0 |          -1 |        -1 | t        | p          | f        | i    
     | f          | f
    59462 | cmin             |       29 |             0 |      4 |     -4 |  
       0 |          -1 |        -1 | t        | p          | f        | i    
     | f          | f
    59462 | xmin             |       28 |             0 |      4 |     -3 |  
       0 |          -1 |        -1 | t        | p          | f        | i    
     | f          | f
    59462 | oid              |       26 |             0 |      4 |     -2 |  
       0 |          -1 |        -1 | t        | p          | f        | i    
     | f          | f
    59462 | ctid             |       27 |             0 |      6 |     -1 |  
       0 |          -1 |        -1 | f        | p          | f        | i    
     | f          | f
    59462 | tripid           |       23 |            10 |      4 |      1 |  
       0 |          -1 |        -1 | t        | p          | f        | i    
     | f          | f
    59462 | tripdatedeparted |     1082 |            10 |      4 |      2 |  
       0 |          -1 |        -1 | t        | p          | f        | i    
     | f          | f
(9 rows)

holidayinfo=# select * from pg_attribute where attrelid=59463;

attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | 
attcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | 
attnotnull | atthasdef
----------+---------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+----------+------------+-----------
(0 rows)

holidayinfo=# select * from pg_attribute where attrelid=59464;

attrelid |        attname         | atttypid | attstattarget | attlen | 
attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | 
attisset | attalign | attnotnull | atthasdef
----------+------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+----------+------------+-----------
    59464 | tableoid               |       26 |             0 |      4 |     
-7 |        0 |          -1 |        -1 | t        | p          | f        | 
i        | f          | f
    59464 | cmax                   |       29 |             0 |      4 |     
-6 |        0 |          -1 |        -1 | t        | p          | f        | 
i        | f          | f
    59464 | xmax                   |       28 |             0 |      4 |     
-5 |        0 |          -1 |        -1 | t        | p          | f        | 
i        | f          | f
    59464 | cmin                   |       29 |             0 |      4 |     
-4 |        0 |          -1 |        -1 | t        | p          | f        | 
i        | f          | f
    59464 | xmin                   |       28 |             0 |      4 |     
-3 |        0 |          -1 |        -1 | t        | p          | f        | 
i        | f          | f
    59464 | oid                    |       26 |             0 |      4 |     
-2 |        0 |          -1 |        -1 | t        | p          | f        | 
i        | f          | f
    59464 | ctid                   |       27 |             0 |      6 |     
-1 |        0 |          -1 |        -1 | f        | p          | f        | 
i        | f          | f
    59464 | tripcityid             |       23 |            10 |      4 |     
  1 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | tripid                 |       23 |            10 |      4 |     
  2 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | cityid                 |       23 |            10 |      4 |     
  3 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | tripcitydatearrived    |     1082 |            10 |      4 |     
  4 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | tripcitydatedeparted   |     1082 |            10 |      4 |     
  5 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | tripcityreasontype     |       25 |            10 |     -1 |     
  6 |        0 |          -1 |        -1 | f        | x          | f        
| i        | f          | f
    59464 | tripcityreasoninfo     |       25 |            10 |     -1 |     
  7 |        0 |          -1 |        -1 | f        | x          | f        
| i        | f          | f
    59464 | tripcityotherpeople    |       25 |            10 |     -1 |     
  8 |        0 |          -1 |        -1 | f        | x          | f        
| i        | f          | f
    59464 | tripcityhotelname      |       25 |            10 |     -1 |     
  9 |        0 |          -1 |        -1 | f        | x          | f        
| i        | f          | f
    59464 | tripcityhoteladdress   |       25 |            10 |     -1 |     
10 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcityhotelurl       |       25 |            10 |     -1 |     
11 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcitymemorybest     |       25 |            10 |     -1 |     
12 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcitymemoryfunniest |       25 |            10 |     -1 |     
13 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcitymemoryworst    |       25 |            10 |     -1 |     
14 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcitydescription    |       25 |            10 |     -1 |     
15 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
(22 rows)

holidayinfo=# select * from pg_attribute where attrelid=59464 and 
attstattarget=10;

attrelid |        attname         | atttypid | attstattarget | attlen | 
attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | 
attisset | attalign | attnotnull | atthasdef
----------+------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+----------+------------+-----------
    59464 | tripcityid             |       23 |            10 |      4 |     
  1 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | tripid                 |       23 |            10 |      4 |     
  2 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | cityid                 |       23 |            10 |      4 |     
  3 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | tripcitydatearrived    |     1082 |            10 |      4 |     
  4 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | tripcitydatedeparted   |     1082 |            10 |      4 |     
  5 |        0 |          -1 |        -1 | t        | p          | f        
| i        | f          | f
    59464 | tripcityreasontype     |       25 |            10 |     -1 |     
  6 |        0 |          -1 |        -1 | f        | x          | f        
| i        | f          | f
    59464 | tripcityreasoninfo     |       25 |            10 |     -1 |     
  7 |        0 |          -1 |        -1 | f        | x          | f        
| i        | f          | f
    59464 | tripcityotherpeople    |       25 |            10 |     -1 |     
  8 |        0 |          -1 |        -1 | f        | x          | f        
| i        | f          | f
    59464 | tripcityhotelname      |       25 |            10 |     -1 |     
  9 |        0 |          -1 |        -1 | f        | x          | f        
| i        | f          | f
    59464 | tripcityhoteladdress   |       25 |            10 |     -1 |     
10 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcityhotelurl       |       25 |            10 |     -1 |     
11 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcitymemorybest     |       25 |            10 |     -1 |     
12 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcitymemoryfunniest |       25 |            10 |     -1 |     
13 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcitymemoryworst    |       25 |            10 |     -1 |     
14 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f
    59464 | tripcitydescription    |       25 |            10 |     -1 |     
15 |        0 |          -1 |        -1 | f        | x          | f        | 
i        | f          | f

_________________________________________________________________
Stay in touch with absent friends - get MSN Messenger 
http://www.msn.co.uk/messenger




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

* Re: dynamic DB descriptions
  2004-08-11 17:02 dynamic DB descriptions Darren Sunley <darrensunley@hotmail.com>
@ 2004-08-13 19:05 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-08-13 19:05 UTC (permalink / raw)
  To: Darren Sunley <darrensunley@hotmail.com>; +Cc: pgsql-admin

"Darren Sunley" <darrensunley@hotmail.com> writes:
> I was just wondering if anyone new of a way of pulling out the attribute 
> names of a table on the fly.

Basically

select attname from pg_attribute where
  attrelid = (select oid from pg_class where relname = 'foo');

For examples of getting fancier, try running psql with -E option and
do "\d foo".  Also read the "system catalogs" chapter in the documentation.

			regards, tom lane



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


end of thread, other threads:[~2004-08-13 19:05 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-11 17:02 dynamic DB descriptions Darren Sunley <darrensunley@hotmail.com>
2004-08-13 19:05 ` Tom Lane <tgl@sss.pgh.pa.us>

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