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