Re: using system catalogs

[Michael Glaesemann <grzm(at)seespotcode(dot)net>]

On Aug 29, 2007, at 13:26 , Mija Lee wrote:

> I'm definitely a postgres newbie, and was wondering about how much  
> can be done with the system
> catalogs. I'd like to a create table that links up with the  
> pg_database catalog so I can track more
> info on each database (users, associated application, status, etc).

Have you looked a the information schema? Does that provide views to  
information you need? If there's something more specific, you can  
always write views to gather this information for you, possibly  
storing some additional information in other tables, as you've  
alluded to below.

> - I assume I cannot and should not attempt to add fields to  
> pg_database

Yup.

> - I have tried to create a table that uses has a reference to  
> pg_database (datname) which it
> complains about. ERROR:  permission denied: "pg_database" is a  
> system catalog_database(datname));

Yup.

> - I can create another table with a field that matches the datname  
> in pg_database and try to write a
> trigger that inserts into my table when a record is inserted into  
> pg_database, but I have the feeling
> that may also be
> a dead end.

You might also want to look at client apps like pgAdmin III: I'm not  
familiar with them, but they may provide convenient views into the  
information you want.

> On a related note, I'm also trying to find the system table that  
> links database oids with table or
> schema oids, but I can't seem to find it. I know it's there  
> somewhere, but don't see it in the doc.

It sounds like you've been looking at the system catalog  
documentation, but I think you should look pg_database, pg_namespace,  
and pg_class in more depth. I think they provide the information  
you're looking for.

Michael Glaesemann
grzm seespotcode net