Re: Show method of index

On May 18, 2009, at 10:25 PM, Tom Lane wrote:
> decibel <decibel(at)decibel(dot)org> writes:
>> The gripe I have with \d is that the "footnotes" are very hard to
>> scan through once you have more than a few things on a table. What
>> I'd like to see is a version that provides the same information, but
>> in a tabular output.
>
> Hmm, I'm not visualizing what you have in mind that would be better?
> The difficulty with the "footnotes" is exactly that the information
> isn't very tabular ...

Instead of...

Indexes:
"debit_cards_pkey" PRIMARY KEY, btree (payment_instrument_id)
Check constraints:
"debit_cards__payment_instrument_type_id_must_equal_1" CHECK
(payment_instrument_type_id = 1)
Foreign-key constraints:
"debit_cards_customer_id_fkey" FOREIGN KEY (customer_id)
REFERENCES customers(id)
"debit_cards_payment_instrument_status_id_fkey" FOREIGN KEY
(payment_instrument_status_id) REFERENCES
payment_instruments.payment_instrument_statuses(id)
"debit_cards_payment_instrument_type_id_fkey" FOREIGN KEY
(payment_instrument_type_id) REFERENCES
payment_instruments.payment_instrument_types(id)
Triggers:
debit_cards__deny_delete BEFORE DELETE ON
payment_instruments.debit_cards FOR EACH STATEMENT EXECUTE PROCEDURE
tools.tg_disallow()
debit_cards__dupe_id BEFORE INSERT OR UPDATE ON
payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE
payment_instruments.tg_payment_instruments_unique()
payment_instrument_status_history AFTER INSERT OR UPDATE ON
payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE
payment_instruments.tg_payment_instrument_status_history()
Inherits: payment_instruments

Something more like...

Inherits: payment_instruments

Indexes:
Name | Options | Method | Columns
------------------+---------+--------+---------------------------
debit_cards_pkey | PRIMARY | btree | payment_instrument_id, ...

Check constraints:
Name |
Constraint
------------------------------------------------------
+-------------------------------
debit_cards__payment_instrument_type_id_must_equal_1 |
payment_instrument_type_id = 1

Foreign-key constraints:
Name | Key
Fields | Schema | Table
| Foreign Keys
-----------------------------------------------
+------------------------------+---------------------
+-----------------------------+--------------
debit_cards_customer_id_fkey |
customer_id | public |
customers | id
debit_cards_payment_instrument_status_id_fkey |
payment_instrument_status_id | payment_instruments |
payment_instrument_statuses | id
debit_cards_payment_instrument_type_id_fkey |
payment_instrument_type_id | payment_instruments |
payment_instrument_types | id

Triggers:
Name | When | DIU | Level |
Schema | Function
-----------------------------------+--------+-----+-----------
+---------------------+---------------------------------------
debit_cards__deny_delete | BEFORE | D | STATEMENT |
tools | tg_disallow()
debit_cards__dupe_id | BEFORE | I | ROW |
payment_instruments | tg_payment_instruments_unique()
payment_instrument_status_history | AFTER | IU | ROW |
payment_instruments | tg_payment_instrument_status_history()

This format is a bit longer, but I think it makes it much easier to
find information, especially on tables that have a lot of footnotes.

It might also be nice to have a command that just shows the options
on a table, and one that just shows the table columns...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828