Re: psql \d option list overloaded - Mailing list pgsql-hackers

Bruce Momjian wrote:

>Alex J. Avriette wrote:
>  
>
>>On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote:
>>
>>    
>>
>>>Anything other than simple, short commands is a waste, IMHO.  I can easily
>>>remember SHOW DATABASES and SHOW TABLES and DESC <table>, because they 
>>>reflect
>>>my intensions directly and 'make sense'.
>>>      
>>>
>>What makes sense to me in csh doesn't make sense in a bourne shell.
>>You can't expect all applications to work correctly. I'd like to second
>>Peter's "yep" when asked if he could remember all the various \d*
>>commands. It really comes down to whether you're trying. New software
>>(even though you may have been using it for a year) requires some
>>adjustment.
>>    
>>
>
>OK, I will drop the idea.  Thanks.
>
>  
>
Bruce,

The idea is not without merit.   What you are looking at is a way to get
this information as a query without having to know all the intricasies
of all the pg_* internals or duplicating complex queries.   "psql -E"
shows you just how tricky this is.  Secondly, if this information
changes in a release, then the end user has to rewrite all of the
queries to work.   Being able to issue a query to the dbms and get the
information as a normal SQL result makes sense and is definately convenient.

The \d* commands work from psql but not from anywhere else.    Try
getting the information from a PHP script by sending a "\dS" query.   It
doesn't work.   If the same queries were stored in the backend and
referenced by psql and also could be referenced by other scripts, this
would be a good thing and keep the work centralized.   If the queries
were in the backend, the psql users could keep the \dS command but it
would call an internal function or execute a queried stored in the
system tables.


One option is to get the information via a function like

SELECT * FROM pg_info('tables');
SELECT * FROM pg_info('indexes');


"psql -E" would show the same query being executed for "\dt"

Another option if no one wanted a language construct, perhaps one option
would be to store the queries themselves in a table like pg_queries. 
This also has the advantage of exposing the queries used so that they
can used as examples for other purposes.

+------------+------------------------------------------+
|pg_info_type|pg_query                                  |
+------------+------------------------------------------+
|tables      |SELECT n.nspname as "Schema",  c.relname  |
|            |as "Name", CASE c.relkind WHEN 'r' THEN   |
|            |'table' WHEN 'v' THEN 'view' WHEN 'i' THEN| 
|            |'index' WHEN 'S' THEN 'sequence' WHEN 's' |
|            |THEN 'special' END as "Type", u.usename as|
|            |"Owner" FROM pg_catalog.pg_class c LEFT   |
|            |JOIN pg_catalog.pg_user u ON u.usesysid = |
|            |c.relowner LEFT JOIN                      |
|            |pg_catalog.pg_namespace n ON n.oid =      |
|            |c.relnamespace WHERE c.relkind IN ('r','')|
|            |AND n.nspname NOT IN ('pg_catalog',       |
|            |'pg_toast') AND                           |
|            |pg_catalog.pg_table_is_visible(c.oid)     |
|            |ORDER BY 1,2;                             |
+------------+------------------------------------------+
|indexes     |SELECT n.nspname as "Schema", c.relname as|
|            |"Name", CASE c.relkind WHEN 'r' THEN      |
|            |'table' WHEN 'v' THEN 'view' WHEN 'i' THEN| 
|            |'index' WHEN 'S' THEN 'sequence' WHEN 's' |
|            |THEN 'special' END as "Type", u.usename as|
|            |"Owner", c2.relname as "Table" FROM       |
|            |pg_catalog.pg_class c JOIN                |
|            |pg_catalog.pg_index i ON i.indexrelid =   |
|            |c.oid JOIN pg_catalog.pg_class c2 ON      |
|            |i.indrelid = c2.oid LEFT JOIN             |
|            |pg_catalog.pg_user u ON u.usesysid =      |
|            |c.relowner LEFT JOIN                      |
|            |pg_catalog.pg_namespace n ON n.oid =      |
|            |c.relnamespace WHERE c.relkind IN ('i','')|
|            |AND n.nspname NOT IN ('pg_catalog',       |
|            |'pg_toast') AND                           | 
|            |pg_catalog.pg_table_is_visible(c.oid)     |
|            |ORDER BY 1,2;                             |
+------------+------------------------------------------+


Again, this is just food for thought.  Perhaps it is a way to satisfy
both arguments.

Thomas