Thread: is there more documentation?
Hi I have been using Postgres for about 7 months now on a part-time basis and have been using the PDF "PostgreSQL 8.1.0 Documentation" and a commercial book as my only sources of documentation. So far the PDF has served me very well - thank-you!! However I am now looking for more information on interrogating the catalog and information_schema. I have about 30 tables and 25 stored procedures (many of my SPs call other SPs), several types and sequences, and still growing. I would like to determine the dependencies between all these objects but the information_schema does not seem to provide this information. From doing some searches it seems like there may be a whole other world of Postgres documentation I have not stumbled upon. Please give me some guidance/direction. -- View this message in context: http://www.nabble.com/is-there-more-documentation--tf3162955.html#a8773756 Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
chrisj <chrisj.wood@sympatico.ca> writes:
> However I am now looking for more information on interrogating the catalog
> and information_schema. I have about 30 tables and 25 stored procedures
> (many of my SPs call other SPs), several types and sequences, and still
> growing. I would like to determine the dependencies between all these
> objects but the information_schema does not seem to provide this
> information.
AFAIK the information_schema doesn't do that. You can get such info
from pg_depend, but there isn't any SQL standard for it.
> From doing some searches it seems like there may be a whole other world of
> Postgres documentation I have not stumbled upon.
Not really, other than the source code and perhaps the techdocs website.
But feel free to ask questions. In this case I'd suggest looking at the
System Catalogs chapter of the manual. Experimentation helps too.
regards, tom lane
chrisj wrote: > Hi > > I have been using Postgres for about 7 months now on a part-time basis and > have been using the PDF "PostgreSQL 8.1.0 Documentation" and a commercial > book as my only sources of documentation. > > So far the PDF has served me very well - thank-you!! > > However I am now looking for more information on interrogating the catalog > and information_schema. I have about 30 tables and 25 stored procedures > (many of my SPs call other SPs), several types and sequences, and still > growing. I would like to determine the dependencies between all these > objects but the information_schema does not seem to provide this > information. information_schema is part of the sql standard as a common way to find various structure information about the database and does not cover every feature of what the dbms can offer. The tables and views inside the pg_catalog schema store all the database info and is where postgresql gets/stores the data it uses internally. Chapter 43 - System Catalogs - details the contents of these tables with a brief description of their use/meaning. This is not an area of huge comprehensive documentation so you won't find your questions directly answered but you will find the info you need to construct the queries you want. The best source may be the source itself, one place to start is the psql source (src/bin/psql/describe.c) can reveal the sql statements it uses to list structure details when requested. You may want to start with - <quote> The catalog pg_depend records the dependency relationships between database objects. This information allows DROP commands to find which other objects must be dropped by DROP CASCADE or prevent dropping in the DROP RESTRICT case. See also pg_shdepend, which performs a similar function for dependencies involving objects that are shared across a database cluster. </quote> >From doing some searches it seems like there may be a whole other world of > Postgres documentation I have not stumbled upon. Please give me some > guidance/direction. > -- Shane Ambler pgSQL@007Marketing.com Get Sheeky @ http://Sheeky.Biz
Thanks Tom, did some experimentation, as you suggested, with some success. Tom Lane-2 wrote: > > chrisj <chrisj.wood@sympatico.ca> writes: >> However I am now looking for more information on interrogating the >> catalog >> and information_schema. I have about 30 tables and 25 stored procedures >> (many of my SPs call other SPs), several types and sequences, and still >> growing. I would like to determine the dependencies between all these >> objects but the information_schema does not seem to provide this >> information. > > AFAIK the information_schema doesn't do that. You can get such info > from pg_depend, but there isn't any SQL standard for it. > >> From doing some searches it seems like there may be a whole other world >> of >> Postgres documentation I have not stumbled upon. > > Not really, other than the source code and perhaps the techdocs website. > But feel free to ask questions. In this case I'd suggest looking at the > System Catalogs chapter of the manual. Experimentation helps too. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > -- View this message in context: http://www.nabble.com/is-there-more-documentation--tf3162955.html#a8830596 Sent from the PostgreSQL - novice mailing list archive at Nabble.com.