Updated patch. Changes:
- returns NULL rather than "cache lookup failed"
- added pg_index_column_has_property (incl. docs)
- added regression tests
Not changed / need consideration:
- this still has everything in amapi.c rather than creating any new
files. Also, the regression tests are in create_index.sql for lack
of any obviously better place.
The list of column properties is:
ordered - (same as "amcanorder" AM capability)
ordered_asc
ordered_desc
ordered_nulls_first
ordered_nulls_last
If "ordered" is true then exactly one of _asc/_desc and exactly one of
_nulls_first/_last will be true; if "ordered" is false then all the
others will be false too. The intended usage is something like
CASE WHEN pg_index_column_has_property(idx, attno, 'ordered_asc')
THEN 'ASC'
WHEN pg_index_column_has_property(idx, attno, 'ordered_desc')
THEN 'DESC'
ELSE '' -- or NULL
END
Comments?
--
Andrew (irc:RhodiumToad)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index ccb9b97..684f7b3 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -577,6 +577,89 @@
</tgroup>
</table>
+ <para>
+ Capability information formerly stored in <structname>pg_am</structname>
+ is now available via the functions
+ <function>pg_indexam_has_capability</function> and
+ <function>pg_index_has_capability</function>
+ (see <xref linkend="functions-info-catalog-table">). The following
+ boolean-valued capability names are currently supported:
+ </para>
+
+ <table>
+ <title>Capabilities</title>
+
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>amcanorder</literal></entry>
+ <entry>Does the access method support ordered scans sorted by the
+ indexed column's value?</entry>
+ </row>
+ <row>
+ <entry><literal>amcanorderbyop</literal></entry>
+ <entry>Does the access method support ordered scans sorted by the result
+ of an operator on the indexed column?</entry>
+ </row>
+ <row>
+ <entry><literal>amcanbackward</literal></entry>
+ <entry>Does the access method support backward scanning?</entry>
+ </row>
+ <row>
+ <entry><literal>amcanunique</literal></entry>
+ <entry>Does the access method support unique indexes?</entry>
+ </row>
+ <row>
+ <entry><literal>amcanmulticol</literal></entry>
+ <entry>Does the access method support multicolumn indexes?</entry>
+ </row>
+ <row>
+ <entry><literal>amoptionalkey</literal></entry>
+ <entry>Does the access method support a scan without any constraint
+ for the first index column?</entry>
+ </row>
+ <row>
+ <entry><literal>amsearcharray</literal></entry>
+ <entry>Does the access method support <literal>ScalarArrayOpExpr</> searches?</entry>
+ </row>
+ <row>
+ <entry><literal>amsearchnulls</literal></entry>
+ <entry>Does the access method support <literal>IS NULL</>/<literal>NOT NULL</> searches?</entry>
+ </row>
+ <row>
+ <entry><literal>amstorage</literal></entry>
+ <entry>Can index storage data type differ from column data type?</entry>
+ </row>
+ <row>
+ <entry><literal>amclusterable</literal></entry>
+ <entry>Can an index of this type be clustered on?</entry>
+ </row>
+ <row>
+ <entry><literal>ampredlocks</literal></entry>
+ <entry>Does an index of this type manage fine-grained predicate locks?</entry>
+ </row>
+ <row>
+ <entry><literal>amgettuple</literal></entry>
+ <entry>Does the access method provide an <function>amgettuple</function> function?</entry>
+ </row>
+ <row>
+ <entry><literal>amgetbitmap</literal></entry>
+ <entry>Does the access method provide an <function>amgetbitmap</function> function?</entry>
+ </row>
+ <row>
+ <entry><literal>amcanreturn</literal></entry>
+ <entry>Does the access method support index-only scans?</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
</sect1>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7830334..d2fe506 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16290,6 +16290,18 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
</indexterm>
<indexterm>
+ <primary>pg_indexam_has_capability</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>pg_index_column_has_property</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>pg_index_has_capability</primary>
+ </indexterm>
+
+ <indexterm>
<primary>pg_options_to_table</primary>
</indexterm>
@@ -16477,6 +16489,21 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
number of columns, pretty-printing is implied</entry>
</row>
<row>
+ <entry><literal><function>pg_indexam_has_capability(<parameter>am_oid</parameter>, <parameter>cap_name</>)</function></literal></entry>
+ <entry><type>boolean</type></entry>
+ <entry>Test whether an index access method has a specified capability</entry>
+ </row>
+ <row>
+ <entry><literal><function>pg_index_column_has_property(<parameter>index_oid</parameter>, <parameter>column_no</>, <parameter>prop_name</>)</function></literal></entry>
+ <entry><type>boolean</type></entry>
+ <entry>Test whether an index column has a specified property</entry>
+ </row>
+ <row>
+ <entry><literal><function>pg_index_has_capability(<parameter>index_oid</parameter>, <parameter>cap_name</>)</function></literal></entry>
+ <entry><type>boolean</type></entry>
+ <entry>Test whether the access method for the specified index has a specified capability</entry>
+ </row>
+ <row>
<entry><literal><function>pg_options_to_table(<parameter>reloptions</parameter>)</function></literal></entry>
<entry><type>setof record</type></entry>
<entry>get the set of storage option name/value pairs</entry>
@@ -16620,6 +16647,73 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
</para>
<para>
+ <function>pg_indexam_has_capability</function> and
+ <function>pg_index_has_capability</function> return whether the specified
+ access method, or the access method for the specified index, advertises the
+ named capability. <literal>NULL</literal> is returned if the capability
+ name is not known; <literal>true</literal> if the capability is advertised,
+ <literal>false</literal> if it is not. Refer
+ to <xref linkend="catalog-pg-am"> for capability names and their meanings.
+ </para>
+
+ <para>
+ <function>pg_index_column_has_property</function> returns whether the
+ specified index column possesses the named property.
+ <literal>NULL</literal> is returned if the property name is not
+ known; <literal>true</literal> if the property is present,
+ <literal>false</literal> if it is not. Index column property names and the
+ matching clauses of <literal>CREATE INDEX</> are given in
+ <xref linkend="functions-info-index-column-props">.
+ </para>
+
+ <table id="functions-info-index-column-props">
+ <title>Index Column Properties</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Clause</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>ordered</literal></entry>
+ <entry></entry>
+ <entry>Does the access method support ordered scans sorted by the
+ column's value?</entry>
+ </row>
+ <row>
+ <entry><literal>ordered_asc</literal></entry>
+ <entry><literal>ASC</></entry>
+ <entry>Does the column sort in ascending order on a forward scan?
+ </entry>
+ </row>
+ <row>
+ <entry><literal>ordered_desc</literal></entry>
+ <entry><literal>DESC</></entry>
+ <entry>Does the column sort in descending order on a forward scan?
+ </entry>
+ </row>
+ <row>
+ <entry><literal>ordered_nulls_first</literal></entry>
+ <entry><literal>NULLS FIRST</></entry>
+ <entry>Does the column sort with nulls first on a forward scan?
+ </entry>
+ </row>
+ <row>
+ <entry><literal>ordered_nulls_last</literal></entry>
+ <entry><literal>NULLS LAST</></entry>
+ <entry>Does the column sort with nulls last on a forward scan?
+ </entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
<function>pg_options_to_table</function> returns the set of storage
option name/value pairs
(<literal>option_name</>/<literal>option_value</>) when passed
diff --git a/src/backend/access/index/amapi.c b/src/backend/access/index/amapi.c
index d347ebc..8f9eb6b 100644
--- a/src/backend/access/index/amapi.c
+++ b/src/backend/access/index/amapi.c
@@ -16,7 +16,10 @@
#include "access/amapi.h"
#include "access/htup_details.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_class.h"
+#include "catalog/pg_index.h"
#include "catalog/pg_opclass.h"
+#include "utils/elog.h"
#include "utils/syscache.h"
@@ -119,3 +122,180 @@ amvalidate(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(result);
}
+
+
+/*
+ * Test capabilities of an index or index AM.
+ */
+static Datum
+indexam_capability(FunctionCallInfo fcinfo,
+ Oid amoid, char *nameptr, int namelen)
+{
+ IndexAmRoutine *routine = NULL;
+ bool ret;
+
+ if (namelen < 1 || namelen > 14)
+ PG_RETURN_NULL();
+
+ routine = GetIndexAmRoutineByAmId(amoid);
+
+ if (namelen == 10 && memcmp(nameptr, "amcanorder", 10) == 0)
+ ret = (routine->amcanorder) ? true : false;
+ else if (namelen == 14 && memcmp(nameptr, "amcanorderbyop", 14) == 0)
+ ret = (routine->amcanorderbyop) ? true : false;
+ else if (namelen == 13 && memcmp(nameptr, "amcanbackward", 13) == 0)
+ ret = (routine->amcanbackward) ? true : false;
+ else if (namelen == 11 && memcmp(nameptr, "amcanunique", 11) == 0)
+ ret = (routine->amcanunique) ? true : false;
+ else if (namelen == 13 && memcmp(nameptr, "amcanmulticol", 13) == 0)
+ ret = (routine->amcanmulticol) ? true : false;
+ else if (namelen == 13 && memcmp(nameptr, "amoptionalkey", 13) == 0)
+ ret = (routine->amoptionalkey) ? true : false;
+ else if (namelen == 13 && memcmp(nameptr, "amsearcharray", 13) == 0)
+ ret = (routine->amsearcharray) ? true : false;
+ else if (namelen == 13 && memcmp(nameptr, "amsearchnulls", 13) == 0)
+ ret = (routine->amsearchnulls) ? true : false;
+ else if (namelen == 9 && memcmp(nameptr, "amstorage", 9) == 0)
+ ret = (routine->amstorage) ? true : false;
+ else if (namelen == 13 && memcmp(nameptr, "amclusterable", 13) == 0)
+ ret = (routine->amclusterable) ? true : false;
+ else if (namelen == 11 && memcmp(nameptr, "ampredlocks", 11) == 0)
+ ret = (routine->ampredlocks) ? true : false;
+ else if (namelen == 11 && memcmp(nameptr, "amcanreturn", 11) == 0)
+ ret = (routine->amcanreturn) ? true : false;
+ else if (namelen == 10 && memcmp(nameptr, "amgettuple", 10) == 0)
+ ret = (routine->amgettuple) ? true : false;
+ else if (namelen == 11 && memcmp(nameptr, "amgetbitmap", 11) == 0)
+ ret = (routine->amgetbitmap) ? true : false;
+ else
+ PG_RETURN_NULL();
+
+ PG_RETURN_BOOL(ret);
+}
+
+/*
+ * Test capability of an AM specified by the AM Oid.
+ */
+Datum
+pg_indexam_has_capability(PG_FUNCTION_ARGS)
+{
+ Oid amoid = PG_GETARG_OID(0);
+ text *name = PG_GETARG_TEXT_PP(1);
+ char *nameptr = VARDATA_ANY(name);
+ int namelen = VARSIZE_ANY_EXHDR(name);
+
+ return indexam_capability(fcinfo, amoid, nameptr, namelen);
+}
+
+/*
+ * Test capability of the AM for an index specified by relation Oid.
+ */
+Datum
+pg_index_has_capability(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ text *name = PG_GETARG_TEXT_PP(1);
+ char *nameptr = VARDATA_ANY(name);
+ int namelen = VARSIZE_ANY_EXHDR(name);
+ Oid amoid;
+ HeapTuple tuple;
+ Form_pg_class rd_rel;
+
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ PG_RETURN_NULL();
+ rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+ if (rd_rel->relkind != RELKIND_INDEX)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("relation %u is not an index", relid)));
+ amoid = rd_rel->relam;
+ ReleaseSysCache(tuple);
+
+ return indexam_capability(fcinfo, amoid, nameptr, namelen);
+}
+
+/*
+ * Test for index column properties
+ */
+Datum
+pg_index_column_has_property(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int32 attno = PG_GETARG_INT32(1);
+ text *propname = PG_GETARG_TEXT_PP(2);
+ char *nameptr = VARDATA_ANY(propname);
+ int namelen = VARSIZE_ANY_EXHDR(propname);
+ Oid amoid;
+ HeapTuple tuple;
+ Form_pg_class rd_rel;
+ Form_pg_index rd_index;
+ int16 natts;
+ Datum indoptionDatum;
+ bool isnull = false;
+ int2vector *indoption;
+ int16 indoption_val;
+ int16 iopt_test = 0;
+ int16 iopt_expect = 0;
+ IndexAmRoutine *routine = NULL;
+
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ PG_RETURN_NULL();
+ rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+ if (rd_rel->relkind != RELKIND_INDEX)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("relation %u is not an index", relid)));
+ amoid = rd_rel->relam;
+ natts = rd_rel->relnatts;
+ ReleaseSysCache(tuple);
+
+ if (attno < 1 || attno > natts)
+ PG_RETURN_NULL();
+
+ routine = GetIndexAmRoutineByAmId(amoid);
+
+ /*
+ * Currently, the only properties of interest are known in the core code,
+ * but in future it might be appropriate to punt unknown property names to
+ * a function in the amhandler.
+ */
+
+ if (namelen == 7 && memcmp(nameptr, "ordered", 7) == 0)
+ PG_RETURN_BOOL(routine->amcanorder);
+ else if (namelen == 11 && memcmp(nameptr, "ordered_asc", 11) == 0)
+ iopt_test = INDOPTION_DESC, iopt_expect = 0;
+ else if (namelen == 12 && memcmp(nameptr, "ordered_desc", 12) == 0)
+ iopt_test = INDOPTION_DESC, iopt_expect = INDOPTION_DESC;
+ else if (namelen == 19 && memcmp(nameptr, "ordered_nulls_first", 19) == 0)
+ iopt_test = INDOPTION_NULLS_FIRST, iopt_expect = INDOPTION_NULLS_FIRST;
+ else if (namelen == 18 && memcmp(nameptr, "ordered_nulls_last", 18) == 0)
+ iopt_test = INDOPTION_NULLS_FIRST, iopt_expect = 0;
+ else
+ PG_RETURN_NULL();
+
+ if (!routine->amcanorder)
+ PG_RETURN_BOOL(false);
+
+ tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ PG_RETURN_NULL();
+ rd_index = (Form_pg_index) GETSTRUCT(tuple);
+
+ Assert(relid == rd_index->indexrelid);
+ Assert(natts == rd_index->indnatts);
+
+ indoptionDatum = SysCacheGetAttr(INDEXRELID, tuple,
+ Anum_pg_index_indoption, &isnull);
+ Assert(!isnull);
+
+ indoption = ((int2vector *) DatumGetPointer(indoptionDatum));
+ indoption_val = indoption->values[attno - 1];
+ ReleaseSysCache(tuple);
+
+ if ((indoption_val & iopt_test) == iopt_expect)
+ PG_RETURN_BOOL(true);
+ else
+ PG_RETURN_BOOL(false);
+}
diff --git a/src/include/access/amapi.h b/src/include/access/amapi.h
index 35f1061..af4e195 100644
--- a/src/include/access/amapi.h
+++ b/src/include/access/amapi.h
@@ -171,4 +171,8 @@ extern IndexAmRoutine *GetIndexAmRoutineByAmId(Oid amoid);
extern Datum amvalidate(PG_FUNCTION_ARGS);
+extern Datum pg_indexam_has_capability(PG_FUNCTION_ARGS);
+extern Datum pg_index_has_capability(PG_FUNCTION_ARGS);
+extern Datum pg_index_column_has_property(PG_FUNCTION_ARGS);
+
#endif /* AMAPI_H */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 270dd21..0cbaab8 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -564,6 +564,11 @@ DESCR("spgist index access method handler");
DATA(insert OID = 335 ( brinhandler PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 325 "2281" _null_ _null_ _null_ _null_ _null_ brinhandler _null_ _null_ _null_ ));
DESCR("brin index access method handler");
+DATA(insert OID = 336 ( pg_indexam_has_capability PGNSP PGUID 12 1 0 0 0 f f f f t f s s 2 0 16 "26 25" _null_ _null_ _null_ _null_ _null_ pg_indexam_has_capability _null_ _null_ _null_ ));
+DESCR("test capability of an index access method");
+DATA(insert OID = 337 ( pg_index_has_capability PGNSP PGUID 12 1 0 0 0 f f f f t f s s 2 0 16 "26 25" _null_ _null_ _null_ _null_ _null_ pg_index_has_capability _null_ _null_ _null_ ));
+DESCR("test capability of an index");
+
DATA(insert OID = 338 ( amvalidate PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 16 "26" _null_ _null_ _null_ _null_ _null_ amvalidate _null_ _null_ _null_ ));
DESCR("validate an operator class");
DATA(insert OID = 3952 ( brin_summarize_new_values PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 23 "2205" _null_ _null_ _null_ _null_ _null_ brin_summarize_new_values _null_ _null_ _null_ ));
@@ -1988,6 +1993,9 @@ DESCR("result type of a function");
DATA(insert OID = 3808 ( pg_get_function_arg_default PGNSP PGUID 12 1 0 0 0 f f f f t f s s 2 0 25 "26 23" _null_ _null_ _null_ _null_ _null_ pg_get_function_arg_default _null_ _null_ _null_ ));
DESCR("function argument default");
+DATA(insert OID = 4032 ( pg_index_column_has_property PGNSP PGUID 12 1 0 0 0 f f f f t f s s 3 0 16 "26 23 25" _null_ _null_ _null_ _null_ _null_ pg_index_column_has_property _null_ _null_ _null_ ));
+DESCR("test boolean property of index column");
+
DATA(insert OID = 1686 ( pg_get_keywords PGNSP PGUID 12 10 400 0 0 f f f f t t s s 0 0 2249 "" "{25,18,25}" "{o,o,o}" "{word,catcode,catdesc}" _null_ _null_ pg_get_keywords _null_ _null_ _null_ ));
DESCR("list of SQL keywords");
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 76593e1..d27a5f8 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2953,6 +2953,89 @@ explain (costs off)
(2 rows)
--
+-- pg_index_column_has_property
+--
+CREATE INDEX onek_props1 ON onek (unique1 desc, unique2 asc, ten nulls first, hundred nulls last);
+select i, prop, pg_index_column_has_property(o, i, prop)
+ from (values ('onek_props1'::regclass)) v1(o),
+ (values (1,'ordered'),(2,'ordered_asc'),(3,'ordered_desc'),
+ (4,'ordered_nulls_first'),(5,'ordered_nulls_last')) v2(idx,prop),
+ generate_series(1,4) i
+ order by i, idx;
+ i | prop | pg_index_column_has_property
+---+---------------------+------------------------------
+ 1 | ordered | t
+ 1 | ordered_asc | f
+ 1 | ordered_desc | t
+ 1 | ordered_nulls_first | t
+ 1 | ordered_nulls_last | f
+ 2 | ordered | t
+ 2 | ordered_asc | t
+ 2 | ordered_desc | f
+ 2 | ordered_nulls_first | f
+ 2 | ordered_nulls_last | t
+ 3 | ordered | t
+ 3 | ordered_asc | t
+ 3 | ordered_desc | f
+ 3 | ordered_nulls_first | t
+ 3 | ordered_nulls_last | f
+ 4 | ordered | t
+ 4 | ordered_asc | t
+ 4 | ordered_desc | f
+ 4 | ordered_nulls_first | f
+ 4 | ordered_nulls_last | t
+(20 rows)
+
+select prop, pg_index_column_has_property(o, 1, prop)
+ from (values ('gcircleind'::regclass)) v1(o),
+ (values (1,'ordered'),(2,'ordered_asc'),(3,'ordered_desc'),
+ (4,'ordered_nulls_first'),(5,'ordered_nulls_last')) v2(idx,prop)
+ order by idx;
+ prop | pg_index_column_has_property
+---------------------+------------------------------
+ ordered | f
+ ordered_asc | f
+ ordered_desc | f
+ ordered_nulls_first | f
+ ordered_nulls_last | f
+(5 rows)
+
+DROP INDEX onek_props1;
+--
+-- index AM capabilites
+--
+select cap,
+ pg_index_has_capability('onek_hundred'::regclass, cap) as btree,
+ pg_index_has_capability('hash_i4_index'::regclass, cap) as hash,
+ pg_index_has_capability('gcircleind'::regclass, cap) as gist,
+ pg_index_has_capability('sp_radix_ind'::regclass, cap) as spgist,
+ pg_index_has_capability('botharrayidx'::regclass, cap) as gin
+ from unnest(array['amcanorder','amcanorderbyop','amcanbackward',
+ 'amcanunique','amcanmulticol','amoptionalkey',
+ 'amsearcharray','amsearchnulls','amstorage',
+ 'amclusterable','ampredlocks','amcanreturn',
+ 'amgettuple','amgetbitmap'
+ ]) with ordinality as u(cap,ord)
+ order by ord;
+ cap | btree | hash | gist | spgist | gin
+----------------+-------+------+------+--------+-----
+ amcanorder | t | f | f | f | f
+ amcanorderbyop | f | f | t | f | f
+ amcanbackward | t | t | f | f | f
+ amcanunique | t | f | f | f | f
+ amcanmulticol | t | f | t | f | t
+ amoptionalkey | t | f | t | t | t
+ amsearcharray | t | f | f | f | f
+ amsearchnulls | t | f | t | t | f
+ amstorage | f | f | t | f | t
+ amclusterable | t | f | t | f | f
+ ampredlocks | t | f | f | f | f
+ amcanreturn | t | f | t | t | f
+ amgettuple | t | t | t | t | f
+ amgetbitmap | t | t | t | t | t
+(14 rows)
+
+--
-- REINDEX (VERBOSE)
--
CREATE TABLE reindex_verbose(id integer primary key);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 71f4f54..62c76e5 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1011,6 +1011,43 @@ explain (costs off)
select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null));
--
+-- pg_index_column_has_property
+--
+CREATE INDEX onek_props1 ON onek (unique1 desc, unique2 asc, ten nulls first, hundred nulls last);
+
+select i, prop, pg_index_column_has_property(o, i, prop)
+ from (values ('onek_props1'::regclass)) v1(o),
+ (values (1,'ordered'),(2,'ordered_asc'),(3,'ordered_desc'),
+ (4,'ordered_nulls_first'),(5,'ordered_nulls_last')) v2(idx,prop),
+ generate_series(1,4) i
+ order by i, idx;
+
+select prop, pg_index_column_has_property(o, 1, prop)
+ from (values ('gcircleind'::regclass)) v1(o),
+ (values (1,'ordered'),(2,'ordered_asc'),(3,'ordered_desc'),
+ (4,'ordered_nulls_first'),(5,'ordered_nulls_last')) v2(idx,prop)
+ order by idx;
+
+DROP INDEX onek_props1;
+
+--
+-- index AM capabilites
+--
+select cap,
+ pg_index_has_capability('onek_hundred'::regclass, cap) as btree,
+ pg_index_has_capability('hash_i4_index'::regclass, cap) as hash,
+ pg_index_has_capability('gcircleind'::regclass, cap) as gist,
+ pg_index_has_capability('sp_radix_ind'::regclass, cap) as spgist,
+ pg_index_has_capability('botharrayidx'::regclass, cap) as gin
+ from unnest(array['amcanorder','amcanorderbyop','amcanbackward',
+ 'amcanunique','amcanmulticol','amoptionalkey',
+ 'amsearcharray','amsearchnulls','amstorage',
+ 'amclusterable','ampredlocks','amcanreturn',
+ 'amgettuple','amgetbitmap'
+ ]) with ordinality as u(cap,ord)
+ order by ord;
+
+--
-- REINDEX (VERBOSE)
--
CREATE TABLE reindex_verbose(id integer primary key);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
