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

My sincere apologies for flooding your mailboxes once again, as the
patch attached in the previous post was incorrect. Also, I had failed
to show test-cases of \d <index> in both 8.4 and 8.3 servers.

Attached are the test cases for psql connecting to 8.4 and 8.3.

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

postgres=# CREATE TABLE foo(a int, b text);
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=#
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=# \d idx_foo_bt_ab;
 Index "public.idx_foo_bt_ab"
 Column |  Type   | Definition
--------+---------+------------
 a      | integer | a
 b      | text    | b
btree, for table "public.foo"

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=# \d idx_foo_bt_fooi;
    Index "public.idx_foo_bt_fooi"
     Column      | Type |  Definition
-----------------+------+--------------
 pg_expression_1 | text | md5(a::text)
 pg_expression_2 | text | md5(a || b)
btree, for table "public.foo"

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 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=# \d idx_foo_bt_ab;
 Index "public.idx_foo_bt_ab"
 Column |  Type   | Definition
--------+---------+------------
 a      | integer | a
 b      | text    | b
btree, for table "public.foo"

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=# \d idx_foo_bt_fooi;
    Index "public.idx_foo_bt_fooi"
     Column      | Type |  Definition
-----------------+------+--------------
 pg_expression_1 | text | md5(a::text)
 pg_expression_2 | text | md5(a || b)
btree, for table "public.foo"

postgres=#

--
Regards,
Khee Chin.