Re: Show method of index

On May 19, 2009, at 9:41 AM, decibel <decibel@decibel.org> wrote:

> On May 18, 2009, at 10:25 PM, Tom Lane wrote:
>> decibel <decibel@decibel.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...

Yes, please!  Many of my tables have as many footnotes as they do  
columns, and it's really annoying when you just want the columns.  But  
what should the syntax be?

I like your other idea too, though it should be an optional behavior,  
I think.

...Robert


>
> -- 
> Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers