agora inbox for postgres@postgres.berkeley.edu
help / color / mirror / Atom feedgleaning the database structure
4+ messages / 4 participants
[nested] [flat]
* gleaning the database structure
@ 1994-08-04 21:57 Stewart Allen <stewart@oec.com>
0 siblings, 3 replies; 4+ messages in thread
From: Stewart Allen @ 1994-08-04 21:57 UTC (permalink / raw)
To: legacy
Is there a simple set of queries which would provide basic table/column
structure information within a database? I've poured over all the .ps
documents that came with 4.0x and the pg_<whatever> structures listed
there do not exactly match those used by the system.
In Informix this information is simply defined in systables and
syscolumns.
I've also encountered problems when searching on the oid. Actually,
I've been completely unable to do it. Whether using the built in
operators or just "table.oid = xxx" it fails with a error about
explicit casts and defining operators for oid and int4 (aren't the
built-in operators supposed to do this?).
I guess one basic question arises from this:
- where do I get better information about oid's, casts, etc?
The FAQ is only minimally useful and I've read all of the available
documentation several times (or maybe I haven't, but I've tried to
be diligent in my research).
--
Stewart Allen
(stewart@oec.com)
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: gleaning the database structure
@ 1994-08-04 23:07 Paul M. Aoki <aoki@CS.Berkeley.EDU>
parent: Stewart Allen <stewart@oec.com>
2 siblings, 0 replies; 4+ messages in thread
From: Paul M. Aoki @ 1994-08-04 23:07 UTC (permalink / raw)
To: Stewart Allen <stewart@oec.com>; +Cc: legacy
Stewart Allen <stewart@oec.com> writes:
> Is there a simple set of queries which would provide basic table/column
> structure information within a database? I've poured over all the .ps
> documents that came with 4.0x and the pg_<whatever> structures listed
> there do not exactly match those used by the system.
look in the documentation for 4.2. (unfortunately the ftp server
is down, probably until the end of next week, since we are still in
the process of moving.)
--
Paul M. Aoki | University of California at Berkeley
aoki@CS.Berkeley.EDU | Dept. of EECS, Computer Science Division (#1776)
| Berkeley, CA 94720-1776
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* gleaning the database structure
@ 1994-08-05 00:20 Mark Costlow <cheeks@swcp.com>
parent: Stewart Allen <stewart@oec.com>
2 siblings, 0 replies; 4+ messages in thread
From: Mark Costlow @ 1994-08-05 00:20 UTC (permalink / raw)
To: stewart@oec.com; +Cc: legacy
On Thu, 4 Aug 1994 17:57:57 -0400 (EDT),
Stewart Allen <stewart@oec.com> said:
Stewart> Is there a simple set of queries which would provide basic
Stewart> table/column structure information within a database?
Here are a few queries that I worked up to do this. I built these by
looking at the code for pg_relshow (in the contributed software that comes
with Postgres).
------------------------------------
To get the list of databases:
retrieve (pg_database.datname)
------------------------------------
Get the list of tables in the current database:
retrieve (pg_class.relname) where pg_class.relname !~ "^pg_"
------------------------------------
Get the list of attributes for a table ("mailees" in this example):
retrieve (FieldNum = a.attnum, FieldName = a.attname, Type = t.typname)
from c in pg_class, a in pg_attribute, t in pg_type
where c.relname = "mailees"
and c.oid = a.attrelid
and a.attnum > 0
and t.oid = a.atttypid
sort by FieldNum using <
------------------------------------
>>Chx
--
Mark Costlow
Southwest Cyberport - Public Access Internet in Albuquerque, NM
Email: cheeks@swcp.com Voice: 505-271-0009
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: gleaning the database structure
@ 1994-08-05 10:18 Thilo Gaul <gaul@ira.uka.de>
parent: Stewart Allen <stewart@oec.com>
2 siblings, 0 replies; 4+ messages in thread
From: Thilo Gaul @ 1994-08-05 10:18 UTC (permalink / raw)
To: stewart@oec.com; +Cc: legacy
> Is there a simple set of queries which would provide basic table/column
> structure information within a database? I've poured over all the .ps
pg_class, pg_type, pg_attribute?
You might need to use Version 4.2, users manual page 27 explains all you
wanted, I think.
> I've also encountered problems when searching on the oid. Actually,
> I've been completely unable to do it. Whether using the built in
> operators or just "table.oid = xxx" it fails with a error about
> explicit casts and defining operators for oid and int4
table.oid? Defining a attribute with a system-class name won't work!
If this should only express the usage of an "oid":
table.attr = "123"::oid will do it...
> - where do I get better information about oid's, casts, etc?
the only reason is, that a number is passed as "int4" and there's no
implicit typecast for "oid"s.
Thilo.
(gaul@ira.uka.de)
==============================================================================
To add/remove yourself to/from the POSTGRES mailing list: send mail with
the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU"
If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
a human will deal with it. DO NOT post to the "postgres" mailing list.
==============================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1994-08-05 10:18 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1994-08-04 21:57 gleaning the database structure Stewart Allen <stewart@oec.com>
1994-08-04 23:07 ` Paul M. Aoki <aoki@CS.Berkeley.EDU>
1994-08-05 00:20 ` Mark Costlow <cheeks@swcp.com>
1994-08-05 10:18 ` Thilo Gaul <gaul@ira.uka.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox