pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Darren Sunley <darrensunley@hotmail.com>
To: pgsql-admin@postgresql.org
Subject: dynamic DB descriptions
Date: Wed, 11 Aug 2004 17:02:04 +0000
Message-ID: <BAY9-F6wu8fmtEqWC7700019457@hotmail.com> (raw)

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




view thread (2+ messages)  latest in thread

Message-ID: <BAY9-F6wu8fmtEqWC7700019457@hotmail.com>
Permalink:  ../BAY9-F6wu8fmtEqWC7700019457@hotmail.com/
Also on:    postgresql.org/message-id/BAY9-F6wu8fmtEqWC7700019457@hotmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: darrensunley@hotmail.com
  Subject: Re: dynamic DB descriptions
  In-Reply-To: <BAY9-F6wu8fmtEqWC7700019457@hotmail.com>
  References: <BAY9-F6wu8fmtEqWC7700019457@hotmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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