Re: psql \d option list overloaded

From: Thomas Swan <tswan(at)idigx(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Subject: Re: psql \d option list overloaded
Date: 2004-01-09 07:07:24
Message-ID: 3FFE532C.2090503@idigx.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-advocacy pgsql-general 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

In response to

Responses

Browse pgsql-advocacy by date

  From Date Subject
Next Message Tommi Maekitalo 2004-01-09 10:19:45 Re: psql \d option list overloaded
Previous Message Bruce Momjian 2004-01-09 05:38:04 Re: Will Open Source be forced to go Proprietary

Browse pgsql-hackers by date

  From Date Subject
Next Message Dennis Björklund 2004-01-09 07:08:59 Translations in the distributions
Previous Message Tom Lane 2004-01-09 05:56:23 Re: How to retrieve functional index column names

Browse pgsql-general by date

  From Date Subject
Next Message Oleg Bartunov 2004-01-09 08:12:28 Re: [HACKERS] Announce: Search PostgreSQL related resources
Previous Message Bruno Wolff III 2004-01-09 05:35:37 Re: 7.4, 'group by' default ordering?