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',
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',
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,
44       pg_index_column_has_property('botharrayidx'::regclass, 1, prop) as gin,
45       pg_index_column_has_property('brinidx'::regclass, 1, prop) as brin
46  from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last',
47                    'orderable', 'distance_orderable', 'returnable',
48                    'search_array', 'search_nulls',
49                    'bogus']::text[])
50         with ordinality as u(prop,ord)
51 order by ord;
52
53select prop,
54       pg_index_has_property('onek_hundred'::regclass, prop) as btree,
55       pg_index_has_property('hash_i4_index'::regclass, prop) as hash,
56       pg_index_has_property('gcircleind'::regclass, prop) as gist,
57       pg_index_has_property('sp_radix_ind'::regclass, prop) as spgist,
58       pg_index_has_property('botharrayidx'::regclass, prop) as gin,
59       pg_index_has_property('brinidx'::regclass, prop) as brin
60  from unnest(array['clusterable', 'index_scan', 'bitmap_scan',
61                    'backward_scan',
62                    'bogus']::text[])
63         with ordinality as u(prop,ord)
64 order by ord;
65
66select amname, prop, pg_indexam_has_property(a.oid, prop) as p
67  from pg_am a,
68       unnest(array['can_order', 'can_unique', 'can_multi_col',
69                    'can_exclude', 'bogus']::text[])
70         with ordinality as u(prop,ord)
71 where amtype = 'i'
72 order by amname, ord;
73
74--
75-- additional checks for pg_index_column_has_property
76--
77CREATE TEMP TABLE foo (f1 int, f2 int, f3 int, f4 int);
78
79CREATE INDEX fooindex ON foo (f1 desc, f2 asc, f3 nulls first, f4 nulls last);
80
81select col, prop, pg_index_column_has_property(o, col, prop)
82  from (values ('fooindex'::regclass)) v1(o),
83       (values (1,'orderable'),(2,'asc'),(3,'desc'),
84               (4,'nulls_first'),(5,'nulls_last'),
85               (6, 'bogus')) v2(idx,prop),
86       generate_series(1,4) col
87 order by col, idx;
88