Re: Show method of index

>> On Tue, May 12, 2009 at 12:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>
>>> Index "public.fooi"
>>> Column | Type | Definition
>>> -----------------+---------+------------
>>> f1 | integer | f1
>>> pg_expression_2 | integer | (f2+f3)
>

Hi,

I'd agree that the mucking around with rulesutil is unorthodox.
Attached is a patch which does the above only modifying, describe . A
prerequisite for column expressions to show is 8.4, as it makes use of
array_agg, in pre 8.4-servers, it uses
pg_get_indexdef(i.indexrelid,0,TRUE)), which I am still unsure whether
we'd want as it stretches the output of \di extremely wide.

- Modifies \di and \d output for indexes

The output whilst connected to a 8.4 server and 8.3 server is as attached,

psql (8.4beta1)
Type "help" for help.

postgres=# CREATE TABLE foo(a int, b text);
CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE TABLE
postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING
btree(md5(a::text), md5(a||b));
CREATE INDEX
postgres=# \div;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+-------------------+-------+-------+-------+--------+-------------------------------
public | idx_foo_bt_ab | index | rubik | foo | btree | a,b
public | idx_foo_bt_fooi | index | rubik | foo | btree |
md5((a)::text), md5((a || b))
public | idx_foo_bt_func | index | rubik | foo | btree | md5((a || b))
public | idx_foo_hash | index | rubik | foo | hash | a
public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a))
(5 rows)

postgres=# \di idx_foo_bt_ab;
List of relations
Schema | Name | Type | Owner | Table | Method | Definition
--------+---------------+-------+-------+-------+--------+------------
public | idx_foo_bt_ab | index | rubik | foo | btree | a,b
(1 row)

postgres=# \di idx_foo_bt_fooi;
List of relations
Schema | Name | Type | Owner | Table | Method | Definition
--------+-----------------+-------+-------+-------+--------+-------------------------------
public | idx_foo_bt_fooi | index | rubik | foo | btree |
md5((a)::text), md5((a || b))
(1 row)

postgres=#

psql (8.4beta1, server 8.3.6)
WARNING: psql version 8.4, server version 8.3.
Some psql features might not work.
Type "help" for help.

postgres=# CREATE TABLE foo(a int, b text);
CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX idx_foo_bt_fooi ON foo USING btree(md5(a::text), md5(a||b));
\div;
\di idx_foo_bt_ab;
\di idx_foo_bt_fooi;
CREATE TABLE
postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING
btree(md5(a::text), md5(a||b));
CREATE INDEX
postgres=# \div;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+-------------------+-------+----------+-------+--------+------------------------------------------------------
public | idx_foo_bt_ab | index | postgres | foo | btree |
CREATE INDEX idx_foo_bt_ab ON foo USING btree (a, b)
public | idx_foo_bt_fooi | index | postgres | foo | btree |
md5((a)::text), md5((a || b))
public | idx_foo_bt_func | index | postgres | foo | btree | md5((a || b))
public | idx_foo_hash | index | postgres | foo | hash |
CREATE INDEX idx_foo_hash ON foo USING hash (a)
public | idx_foo_hash_func | index | postgres | foo | hash | md5((b || a))
(5 rows)

postgres=# \di idx_foo_bt_ab;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+---------------+-------+----------+-------+--------+------------------------------------------------------
public | idx_foo_bt_ab | index | postgres | foo | btree | CREATE
INDEX idx_foo_bt_ab ON foo USING btree (a, b)
(1 row)

postgres=# \di idx_foo_bt_fooi;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+-----------------+-------+----------+-------+--------+-------------------------------
public | idx_foo_bt_fooi | index | postgres | foo | btree |
md5((a)::text), md5((a || b))
(1 row)

postgres=#

Regards,
Khee Chin.