pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddynamic 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