On Thu, Mar 25, 2010 at 1:26 AM, John R Pierce <pierce@xxxxxxxxxxxx> wrote:
Yogi Yang 007 wrote:
Hello,
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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
