Re: Show method of index - Mailing list pgsql-hackers

>> On Tue, May 12, 2009 at 12:20 AM, Tom Lane <tgl@sss.pgh.pa.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.