X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6C61F14B2820 for ; Wed, 11 Aug 2004 14:02:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26074-09 for ; Wed, 11 Aug 2004 17:02:08 +0000 (GMT) Received: from hotmail.com (bay9-f6.bay9.hotmail.com [64.4.47.6]) by svr1.postgresql.org (Postfix) with ESMTP id DB49214B281C for ; Wed, 11 Aug 2004 14:02:04 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 11 Aug 2004 10:02:04 -0700 Received: from 163.166.150.21 by by9fd.bay9.hotmail.msn.com with HTTP; Wed, 11 Aug 2004 17:02:04 GMT X-Originating-IP: [163.166.150.21] X-Originating-Email: [darrensunley@hotmail.com] X-Sender: darrensunley@hotmail.com From: "Darren Sunley" To: pgsql-admin@postgresql.org Subject: dynamic DB descriptions Date: Wed, 11 Aug 2004 17:02:04 +0000 Mime-Version: 1.0 Content-Type: text/plain; format=flowed Message-ID: X-OriginalArrivalTime: 11 Aug 2004 17:02:04.0961 (UTC) FILETIME=[ED96B110:01C47FC4] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/106 X-Sequence-Number: 14501 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