1-- 2-- Test index AM property-reporting functions 3-- 4 5select prop, 6 pg_indexam_has_property(a.oid, prop) as "AM", 7 pg_index_has_property('onek_hundred'::regclass, prop) as "Index", 8 pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as "Column" 9 from pg_am a, 10 unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', 11 'orderable', 'distance_orderable', 'returnable', 12 'search_array', 'search_nulls', 13 'clusterable', 'index_scan', 'bitmap_scan', 14 'backward_scan', 15 'can_order', 'can_unique', 'can_multi_col', 16 'can_exclude', 'can_include', 17 'bogus']::text[]) 18 with ordinality as u(prop,ord) 19 where a.amname = 'btree' 20 order by ord; 21 22select prop, 23 pg_indexam_has_property(a.oid, prop) as "AM", 24 pg_index_has_property('gcircleind'::regclass, prop) as "Index", 25 pg_index_column_has_property('gcircleind'::regclass, 1, prop) as "Column" 26 from pg_am a, 27 unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', 28 'orderable', 'distance_orderable', 'returnable', 29 'search_array', 'search_nulls', 30 'clusterable', 'index_scan', 'bitmap_scan', 31 'backward_scan', 32 'can_order', 'can_unique', 'can_multi_col', 33 'can_exclude', 'can_include', 34 'bogus']::text[]) 35 with ordinality as u(prop,ord) 36 where a.amname = 'gist' 37 order by ord; 38 39select prop, 40 pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as btree, 41 pg_index_column_has_property('hash_i4_index'::regclass, 1, prop) as hash, 42 pg_index_column_has_property('gcircleind'::regclass, 1, prop) as gist, 43 pg_index_column_has_property('sp_radix_ind'::regclass, 1, prop) as spgist_radix, 44 pg_index_column_has_property('sp_quad_ind'::regclass, 1, prop) as spgist_quad, 45 pg_index_column_has_property('botharrayidx'::regclass, 1, prop) as gin, 46 pg_index_column_has_property('brinidx'::regclass, 1, prop) as brin 47 from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', 48 'orderable', 'distance_orderable', 'returnable', 49 'search_array', 'search_nulls', 50 'bogus']::text[]) 51 with ordinality as u(prop,ord) 52 order by ord; 53 54select prop, 55 pg_index_has_property('onek_hundred'::regclass, prop) as btree, 56 pg_index_has_property('hash_i4_index'::regclass, prop) as hash, 57 pg_index_has_property('gcircleind'::regclass, prop) as gist, 58 pg_index_has_property('sp_radix_ind'::regclass, prop) as spgist, 59 pg_index_has_property('botharrayidx'::regclass, prop) as gin, 60 pg_index_has_property('brinidx'::regclass, prop) as brin 61 from unnest(array['clusterable', 'index_scan', 'bitmap_scan', 62 'backward_scan', 63 'bogus']::text[]) 64 with ordinality as u(prop,ord) 65 order by ord; 66 67select amname, prop, pg_indexam_has_property(a.oid, prop) as p 68 from pg_am a, 69 unnest(array['can_order', 'can_unique', 'can_multi_col', 70 'can_exclude', 'can_include', 'bogus']::text[]) 71 with ordinality as u(prop,ord) 72 where amtype = 'i' 73 order by amname, ord; 74 75-- 76-- additional checks for pg_index_column_has_property 77-- 78CREATE TEMP TABLE foo (f1 int, f2 int, f3 int, f4 int); 79 80CREATE INDEX fooindex ON foo (f1 desc, f2 asc, f3 nulls first, f4 nulls last); 81 82select col, prop, pg_index_column_has_property(o, col, prop) 83 from (values ('fooindex'::regclass)) v1(o), 84 (values (1,'orderable'),(2,'asc'),(3,'desc'), 85 (4,'nulls_first'),(5,'nulls_last'), 86 (6, 'bogus')) v2(idx,prop), 87 generate_series(1,4) col 88 order by col, idx; 89 90CREATE INDEX foocover ON foo (f1) INCLUDE (f2,f3); 91 92select col, prop, pg_index_column_has_property(o, col, prop) 93 from (values ('foocover'::regclass)) v1(o), 94 (values (1,'orderable'),(2,'asc'),(3,'desc'), 95 (4,'nulls_first'),(5,'nulls_last'), 96 (6,'distance_orderable'),(7,'returnable'), 97 (8, 'bogus')) v2(idx,prop), 98 generate_series(1,3) col 99 order by col, idx; 100