Re: Improved index support for \d and \di in psql

This looks real good. I will save it for 7.3.

---------------------------------------------------------------------------

Content-Description: Mail message body

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Here is a better version that allows psql to view functional and
> partial indexes. (btw, my previous problem regarding the
> partial-index predicate was an extra comma in a select statement)
>
> I've used pg_get_indexdef (in most cases) for the advanced index
> information, and put it in the SQL rather than having C
> try and parse the string apart.
>
> I also looked in the documentation, but did not find any spots
> that need changing. The only thing that seemd to mention
> "\d" in docs is the release notes and
> sgml/ref/psql-ref.sgml, which is very vague and has no examples
> that need changing. Perhaps that example section should be
> expanded a bit?
>
> Here is what the psql output looks like after the patch:
>
> (table with a normal, a functional, and a partial index)
>
> template1=# \d foo
>
> Table "foo"
> Column | Type | Modifiers
> - --------+-----------------------+-----------
> foo | integer |
> bar | integer |
> baz | character varying(20) |
> Indexes: abc btree (foo) WHERE (foo < 100),
> funky btree (lower(baz)),
> simple btree (foo)
>
>
> (all indexes now list waht table they belong to)
>
> template1=# \di
>
> List of relations
> Name | Type | Owner | Table
> - --------+-------+----------+-------
> abc | index | postgres | foo
> four | index | postgres | ftest
> funky | index | postgres | foo
> one | index | postgres | ftest
> simple | index | postgres | foo
> three | index | postgres | ftest
> two | index | postgres | ftest
>
>
> (a partial index)
>
> template1=# \d abc
>
> Index "abc"
> Column | Type
> - --------+---------
> foo | integer
> btree for table "foo" WHERE (foo < 100)
>
>
> (a functional index - much improved over just showing 'lower')
>
> template1=# \d funky
>
> Index "funky"
> Column | Type
> - --------------+------
> (lower(baz)) | text
> btree for table "foo"
>
>
> Greg Sabino Mullane
> greg(at)turnstep(dot)com
> PGP Key: 0x14964AC8 200111191050
>
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iQA/AwUBO/kqr7ybkGcUlkrIEQKzZQCfe3Bmbx3/c2YTOgifxK242KvyvSsAoPZf
> vmVJtwAaYA2S4P0fqCaQR2Zm
> =ZBN+
> -----END PGP SIGNATURE-----
>

Content-Description: Attachment information.

> The following section of this message contains a file attachment
> prepared for transmission using the Internet MIME message format.
> If you are using Pegasus Mail, or any another MIME-compliant system,
> you should be able to save it or view it from within your mailer.
> If you cannot, please ask your system administrator for assistance.
>
> ---- File information -----------
> File: indexdetail2.patch
> Date: 19 Nov 2001, 11:23
> Size: 10939 bytes.
> Type: Unknown

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026