Darren Sunley
2004-08-11 17:02:04 UTC
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html