I am new to pgSQL. I would like to know if there is a way to do the following using pure SQL: 1. Retrieve list of all Tables in a database 2. Retrieve list of all Functions, Triggers, Sequences, Views, etc. 3. Retrieve list of all Group Roles 4. Retrieve list of all Login Roles 5. Structure of a Table
If you want to learn the postgres catalogs, fire up 'psql -E'
When you use the meta-commands like \dt -- lists tables.... then it will show you the SQL it ran to generate the table list. Run those meta-commands and pay attention to the emitted SQL, it's helpful.
--Scott
most of that stuff can be fetched from the INFORMATION_SCHEMA, such as ..
select * from information_schema.tables where table_schema not in ('pg_catalog','information_schema');
select * from information_schema.columns where table_schema='schemaname' and table_name='tablename';
note, * on both of these returns a lot of data, you may want to be more selective, depending on just what infoyou need to know, especially about the columns.
