Please describe yourself
Ok, so my brain is right now thinking on how to make Comas (my Conference Management System, which is quite nice, but somewhat unflexible. Sorry for the butt-ugly and incomplete webpage…) more flexible - And I have come accross some quite nice things. First of all, I want the fields for each person (the person table) to be easily modifiable, allowing me to add/remove attributes at will between different instances of Comas. Ok, this is the muscle behind my new idea. Yes, PostgreSQL-specific, as basically everything in Comas:
CREATE OR REPLACE VIEW table_attributes AS SELECT c.relname AS tablename, a.attname AS attrname, t.typname AS type, a.attnotnull AS required, d.description AS description FROM pg_class c JOIN pg_attribute a ON a.attrelid=c.oid JOIN pg_type t ON a.atttypid=t.oid LEFT OUTER JOIN pg_description d ON d.objoid=c.oid AND d.objsubid=a.attnum WHERE a.attnum>0 AND a.attisdropped = 'f' AND c.relkind='r' AND c.relname NOT LIKE 'pg_%' AND c.relname NOT LIKE 'sql_%' ORDER BY tablename, a.attnum;
Nice, but still not enough - As with any other RDBMS, fields can not only hold data, but -of course- they can be foreign keys, referring to data in other tables. Ok, in order to check those relations and be able to build a map of the relations in my DB, I came up with this:
CREATE OR REPLACE VIEW related_tables AS SELECT c1.relname AS referrer, c2.relname AS refered, a.attname AS ref_key FROM pg_constraint con JOIN pg_class c1 ON con.conrelid=c1.oid JOIN pg_class c2 ON con.confrelid=c2.oid JOIN pg_attribute a ON c1.oid=a.attrelid AND a.attnum=ANY(con.conkey) WHERE contype='f' ORDER BY c1.relname, c2.relname;
In the first query, note that I am excluding from what I report all the tables starting with pg_ or sql_ - While that’s a usual convention in Postgres, there surely is a better way to do it, some attribute signalling it’s a system catalog… But, at least for now, this covers my needs. Anyway, this might come useful for your projects. PostgreSQL introspection is fun! (Yes, those two queries are the result of many lonely hours going through the documentation and a couple of spontaneous questions to several friends - But it’s worth it!)
Comments
Alvaro Herrera 2005-10-26 18:27:47
RE: Please describe yourself
Regarding excluding the pg_% and sql_% tables, note that that is a pretty old and deprecated convention. With the introduction of schemas in 7.3, it became possible to distinguish system tables by schema rather than name. The schema for system catalogs is pg_catalog; additionally, most people like to exclude information_schema because it only contains tables defined by the SQL standard.
So the query ends up with a condition like
WHERE … and pg_class.relnamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname IN (‘pg_catalog’, ‘information_schema’))
Hope that helps.
Alvaro Herrera 2005-10-26 18:30:11
RE: Please describe yourself
Oops, the comment system interpreted underscores in my text as marks to italicize the text. Not sure how to write it to prevent that …