diff options
Diffstat (limited to 'src/test/regress/expected/amutils.out')
| -rw-r--r-- | src/test/regress/expected/amutils.out | 254 |
1 files changed, 254 insertions, 0 deletions
diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out new file mode 100644 index 0000000..d92a6d1 --- /dev/null +++ b/src/test/regress/expected/amutils.out @@ -0,0 +1,254 @@ +-- +-- Test index AM property-reporting functions +-- +select prop, + pg_indexam_has_property(a.oid, prop) as "AM", + pg_index_has_property('onek_hundred'::regclass, prop) as "Index", + pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as "Column" + from pg_am a, + unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', + 'clusterable', 'index_scan', 'bitmap_scan', + 'backward_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude', 'can_include', + 'bogus']::text[]) + with ordinality as u(prop,ord) + where a.amname = 'btree' + order by ord; + prop | AM | Index | Column +--------------------+----+-------+-------- + asc | | | t + desc | | | f + nulls_first | | | f + nulls_last | | | t + orderable | | | t + distance_orderable | | | f + returnable | | | t + search_array | | | t + search_nulls | | | t + clusterable | | t | + index_scan | | t | + bitmap_scan | | t | + backward_scan | | t | + can_order | t | | + can_unique | t | | + can_multi_col | t | | + can_exclude | t | | + can_include | t | | + bogus | | | +(19 rows) + +select prop, + pg_indexam_has_property(a.oid, prop) as "AM", + pg_index_has_property('gcircleind'::regclass, prop) as "Index", + pg_index_column_has_property('gcircleind'::regclass, 1, prop) as "Column" + from pg_am a, + unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', + 'clusterable', 'index_scan', 'bitmap_scan', + 'backward_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude', 'can_include', + 'bogus']::text[]) + with ordinality as u(prop,ord) + where a.amname = 'gist' + order by ord; + prop | AM | Index | Column +--------------------+----+-------+-------- + asc | | | f + desc | | | f + nulls_first | | | f + nulls_last | | | f + orderable | | | f + distance_orderable | | | t + returnable | | | f + search_array | | | f + search_nulls | | | t + clusterable | | t | + index_scan | | t | + bitmap_scan | | t | + backward_scan | | f | + can_order | f | | + can_unique | f | | + can_multi_col | t | | + can_exclude | t | | + can_include | t | | + bogus | | | +(19 rows) + +select prop, + pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as btree, + pg_index_column_has_property('hash_i4_index'::regclass, 1, prop) as hash, + pg_index_column_has_property('gcircleind'::regclass, 1, prop) as gist, + pg_index_column_has_property('sp_radix_ind'::regclass, 1, prop) as spgist_radix, + pg_index_column_has_property('sp_quad_ind'::regclass, 1, prop) as spgist_quad, + pg_index_column_has_property('botharrayidx'::regclass, 1, prop) as gin, + pg_index_column_has_property('brinidx'::regclass, 1, prop) as brin + from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', + 'bogus']::text[]) + with ordinality as u(prop,ord) + order by ord; + prop | btree | hash | gist | spgist_radix | spgist_quad | gin | brin +--------------------+-------+------+------+--------------+-------------+-----+------ + asc | t | f | f | f | f | f | f + desc | f | f | f | f | f | f | f + nulls_first | f | f | f | f | f | f | f + nulls_last | t | f | f | f | f | f | f + orderable | t | f | f | f | f | f | f + distance_orderable | f | f | t | f | t | f | f + returnable | t | f | f | t | t | f | f + search_array | t | f | f | f | f | f | f + search_nulls | t | f | t | t | t | f | t + bogus | | | | | | | +(10 rows) + +select prop, + pg_index_has_property('onek_hundred'::regclass, prop) as btree, + pg_index_has_property('hash_i4_index'::regclass, prop) as hash, + pg_index_has_property('gcircleind'::regclass, prop) as gist, + pg_index_has_property('sp_radix_ind'::regclass, prop) as spgist, + pg_index_has_property('botharrayidx'::regclass, prop) as gin, + pg_index_has_property('brinidx'::regclass, prop) as brin + from unnest(array['clusterable', 'index_scan', 'bitmap_scan', + 'backward_scan', + 'bogus']::text[]) + with ordinality as u(prop,ord) + order by ord; + prop | btree | hash | gist | spgist | gin | brin +---------------+-------+------+------+--------+-----+------ + clusterable | t | f | t | f | f | f + index_scan | t | t | t | t | f | f + bitmap_scan | t | t | t | t | t | t + backward_scan | t | t | f | f | f | f + bogus | | | | | | +(5 rows) + +select amname, prop, pg_indexam_has_property(a.oid, prop) as p + from pg_am a, + unnest(array['can_order', 'can_unique', 'can_multi_col', + 'can_exclude', 'can_include', 'bogus']::text[]) + with ordinality as u(prop,ord) + where amtype = 'i' + order by amname, ord; + amname | prop | p +--------+---------------+--- + brin | can_order | f + brin | can_unique | f + brin | can_multi_col | t + brin | can_exclude | f + brin | can_include | f + brin | bogus | + btree | can_order | t + btree | can_unique | t + btree | can_multi_col | t + btree | can_exclude | t + btree | can_include | t + btree | bogus | + gin | can_order | f + gin | can_unique | f + gin | can_multi_col | t + gin | can_exclude | f + gin | can_include | f + gin | bogus | + gist | can_order | f + gist | can_unique | f + gist | can_multi_col | t + gist | can_exclude | t + gist | can_include | t + gist | bogus | + hash | can_order | f + hash | can_unique | f + hash | can_multi_col | f + hash | can_exclude | t + hash | can_include | f + hash | bogus | + spgist | can_order | f + spgist | can_unique | f + spgist | can_multi_col | f + spgist | can_exclude | t + spgist | can_include | f + spgist | bogus | +(36 rows) + +-- +-- additional checks for pg_index_column_has_property +-- +CREATE TEMP TABLE foo (f1 int, f2 int, f3 int, f4 int); +CREATE INDEX fooindex ON foo (f1 desc, f2 asc, f3 nulls first, f4 nulls last); +select col, prop, pg_index_column_has_property(o, col, prop) + from (values ('fooindex'::regclass)) v1(o), + (values (1,'orderable'),(2,'asc'),(3,'desc'), + (4,'nulls_first'),(5,'nulls_last'), + (6, 'bogus')) v2(idx,prop), + generate_series(1,4) col + order by col, idx; + col | prop | pg_index_column_has_property +-----+-------------+------------------------------ + 1 | orderable | t + 1 | asc | f + 1 | desc | t + 1 | nulls_first | t + 1 | nulls_last | f + 1 | bogus | + 2 | orderable | t + 2 | asc | t + 2 | desc | f + 2 | nulls_first | f + 2 | nulls_last | t + 2 | bogus | + 3 | orderable | t + 3 | asc | t + 3 | desc | f + 3 | nulls_first | t + 3 | nulls_last | f + 3 | bogus | + 4 | orderable | t + 4 | asc | t + 4 | desc | f + 4 | nulls_first | f + 4 | nulls_last | t + 4 | bogus | +(24 rows) + +CREATE INDEX foocover ON foo (f1) INCLUDE (f2,f3); +select col, prop, pg_index_column_has_property(o, col, prop) + from (values ('foocover'::regclass)) v1(o), + (values (1,'orderable'),(2,'asc'),(3,'desc'), + (4,'nulls_first'),(5,'nulls_last'), + (6,'distance_orderable'),(7,'returnable'), + (8, 'bogus')) v2(idx,prop), + generate_series(1,3) col + order by col, idx; + col | prop | pg_index_column_has_property +-----+--------------------+------------------------------ + 1 | orderable | t + 1 | asc | t + 1 | desc | f + 1 | nulls_first | f + 1 | nulls_last | t + 1 | distance_orderable | f + 1 | returnable | t + 1 | bogus | + 2 | orderable | f + 2 | asc | + 2 | desc | + 2 | nulls_first | + 2 | nulls_last | + 2 | distance_orderable | f + 2 | returnable | t + 2 | bogus | + 3 | orderable | f + 3 | asc | + 3 | desc | + 3 | nulls_first | + 3 | nulls_last | + 3 | distance_orderable | f + 3 | returnable | t + 3 | bogus | +(24 rows) + |