Re: [GENERAL] How can I obtain tables' structure? - Mailing list pgsql-general

Hey,

I did some messing around with this one night....

I haven't found anything comprehensive and totally conclusive, but I think
I made some progress that you may be able to build upon.

For all of those interested, I've attached some text that I wrote after I
was done "messing around."  I would happily take any commentary that might
be initiated by this...

Hope this helps,

Bob



On Mon, 7 Dec 1998, Paul Mookhachov wrote:

> Return-Path: <owner-pgsql-general@hub.org>
> Received: from hub.org (majordom@hub.org [209.47.148.200])
>     by farout.palaver.net (8.9.1/8.9.1) with ESMTP id JAA29182
>     for <bobd@palaver.net>; Mon, 7 Dec 1998 09:07:05 -0500
> Received: from localhost (majordom@localhost)
>     by hub.org (8.9.1/8.9.1) with SMTP id IAA06502;
>     Mon, 7 Dec 1998 08:55:45 -0500 (EST)
>     (envelope-from owner-pgsql-general@hub.org)
> Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Mon, 07 Dec 1998 08:45:37 +0000 (EST)
> Received: (from majordom@localhost)
>     by hub.org (8.9.1/8.9.1) id IAA04424
>     for pgsql-general-outgoing; Mon, 7 Dec 1998 08:45:34 -0500 (EST)
>     (envelope-from owner-pgsql-general@postgreSQL.org)
> Received: from trends.net (root@clio.trends.ca [209.47.148.2])
>     by hub.org (8.9.1/8.9.1) with ESMTP id IAA04356
>     for <pgsql-general@postgreSQL.org>; Mon, 7 Dec 1998 08:45:20 -0500 (EST)
>     (envelope-from paul@pmfd.ru)
> Received: from pmfd.pmfd.ru (pmfd.pmfd.ru [194.8.185.65])
>     by trends.net (8.8.8/8.8.8) with SMTP id FAA01679
>     for <pgsql-general@postgreSQL.org>; Mon, 7 Dec 1998 05:53:54 -0500 (EST)
> Received: from bridge.pmfd.ru by pmfd.pmfd.ru id aa27100; 7 Dec 98 11:37 MSK
> Message-ID: <366B9494.DB7C5713@pmfd.ru>
> Date: Mon, 07 Dec 1998 11:40:52 +0300
> From: Paul Mookhachov <paul@pmfd.ru>
> Reply-To: paul@pmfd.ru
> Organization: PMFD
> X-Mailer: Mozilla 4.5 [en] (WinNT; I)
> X-Accept-Language: ru
> MIME-Version: 1.0
> To: pgsql-general@postgreSQL.org
> Subject: Re: [GENERAL] How can I obtain tables' structure?
> References: <F10BB1FAF801D111829B0060971D839F54EF3E@cpsmail>
> Content-Type: text/plain; charset=koi8-r
> Content-Transfer-Encoding: 7bit
> Sender: owner-pgsql-general@postgreSQL.org
> Precedence: bulk
>
> "Jackson, DeJuan" wrote:
>
> > >
> > > > How can I obtain information about tables' structure and list of
> > > tables
> > > > using psql.lib or Pg.pm (perl package)?
> > >
> > >       all you have to do is to make a request likt this :
> > > 'select * from <table>' ($req = $cbase->exec ('select * from table'))
> > I'd use 'select * from <table> where false'. It save the table scan and
>
> Well. I use same way. But may be I can do "select" from some system table?
> There are several tables called pg_*. They contain miscellaneous information,
> but I can't understand it.
>
> ------------------------
> Paul E Mookhachov                  paul@pmfd.ru
> System administrator of            phone: +7 /812/ 329-8102
> St-Petersburg Interbank            St-Petersbirg
> Financial House                    B.Zelenina st, 24
>
>

I was wondering, tonight, how I could find out the names of all the
attributes of a given table in my database (and the types of the
attributes, etc..) without creating "extra" meta tables.

Well, it appears that I've figured it out.  So, the rest of this message
is a description of how one might go about doing so... just in case you
ever need such info.

First, I asked 'Pouch for help.  And contrary to what Capouch might tell
folks, my request for help did not include the phrases "Please, dad?!"
or "I'm just so helpless!!"  Regardless, he was immediately able to
figure out that I meant business about finding the desired info (maybe
that's why we all love him so much). Anyway, he and I then probed around
in the bowels of Postgres and found the "meta tables" that describe a
database, in terms of its tables, its attributes, etc...

For any particular database, the names of all the tables in that
database can be found in the table pg_class (select * from pg_class;). 
However, since Postgres is keeping track of a lot of shit - it has more
tables listed for your database than what you've created.  So, to
extract all the tables of your creation from pg_class, you perform the
following query:  select * from pg_class where not (relname ~* 'pg_');

That query returns a list of all your tables to you.  It tells you the
names of the tables, who owns them... and, all kinds of other things
(which appear to be stored using the Greek alphabet).  

Now, you might be asking, "Well, Bob, now I remember you mentioning
something about finding out what all the attributes are for your tables
(ie. all the field names in your tables)... how did you figure that
out?"  

Well, all your table attributes are stored in a table named
pg_attributes (select * from pg_attributes;).  But, just the same as
pg_class contains all of your table names AND all of the special table
names that Postgres keeps track of (so that Postgres may keep track of
all the fancy stuff that it keeps track of)... pg_attributes contains
all of your table attributes AND all of the attributes for Postgres's
tables.

So, if you would like to obtain all of your own table attributes (or
just one of your own table's attributes), you must know the proper id
numbers of your tables (or of just the one table).

As it turns out, to figure out the id numbers of your tables is not a
trivial matter.  One would think (at least I did) that the query I gave
you above (the one that returned the table names) would return table id
numbers... but, it doesn't.  So, where do you look?

Well, the answer - my friends (is blowin' in the wind... sorry) - lies
in a table named pg_type (select * from pg_type).  pg_type contains a
wild array of information.  It stores a bunch of information for
datatypes.  It stores all the "table name"/"table id number"
associations for Postgres's tables.  And, it stores all of the "table
name"/"table id number" associations for your own tables (as well as
some other stuff that also appears to be stored using the Greek
alphabet).  The trick, here, is to extract only the information you need
(which, for the purpose at hand, is to extract only the information that
pertains to your tables).

As it turns out, all of the instances or entries in the table pg_type
have an attribute labelled "typtype".  This attribute takes on three
different values:  a, b, c. I have no idea what information is
represented with a "typtype" of value 'a'.  However, I know that all of
the datatype information is represented by a "typtype" value of 'b'. 
And, furthermore, I know that the table information is represented by
the "typtype" value 'c'... note, once again, that when I say "table
information" I am referring to both your own tables and Postgres's
tables.  

So to dig further and to extract only your own table information from
the table pg_type, you must perform the following query:

    select * from pg_type where typtype = 'c' and not (typname ~* 'pg_');

Ahhh, the sweet feeling of success.  Once you've executed this query,
you can associate all of your table names with a table id number
(represented in pg_type by the attribute 'typrelid') and, now, you may
go back and obtain all of your attribute information:

    select * from pg_attribute where attrelid in ( select typrelid from
pg_type where typtype = 'c' and not (typname ~* 'pg_'));

Yeah... you have to do a little subquery action... but, that never hurt
anyone.  So, if this were a perfect world, you'd be finished.  You'd
have all the pretty information you needed and you'd be rewarded for all
of your hardwork with fine women (or men, depending on your
preference), fine liquor and all sorts of miscellaneous crap.

But, as we all know... even though this may be the best of all possible
worlds, it ain't prefect.  So, we've got some more work to do to
actually make sense out of your query.  First things first - the
previous query returns some more Greek characters.  We can quickly rid
ourselves of those if we perform the following query (which is just an
extension of the previous one):

    select * from pg_attribute where attrelid in ( select typrelid from
pg_type where typtype = 'c' and not (typname ~* 'pg_')) and attnum > 0;

Now, we're in business.  We've now got integer representations of all
attribute data types, there data type lengths, their names and the
tables they belong to.  The next step is going to be to determine the
actual "understandable" data types, lengths, etc.. by using these
integer representations.

But, we'll have to do that some other time.  I'm happy and tired.  I
think I'm gonna go home and wet the bed.

'Night all,

Bob