|
Hello Postgres Gurus, I am doing some research regarding the postgres native xml
type, I found that the xml type can not be indexed, I found some work arounds
for it but, I was wondering if there were any plans to implement indexing on a
xpath _expression_ in future releases on Postges like Postgres 9? Thank you, Sncerely, Kasia -----------------------------------------------------------------------------------------------------------------------------------------------------8.3
has integrated xpath function. There is gap in XML support, because XML type
isn't supported with GIST or GIN index. So xpath function returns array of xml
values. But we can write custom casting to int array: CREATE
OR REPLACE FUNCTION xml_list_to_int(xml[]) RETURNS
int[] AS $$ SELECT
ARRAY(SELECT to_number(($1[i])::text,'999999.99')::int
FROM generate_series(1, array_upper($1,1)) g(i)) $$
LANGUAGE SQL IMMUTABLE; CREATE
CAST (xml[] AS int[]) WITH FUNCTION xml_list_to_int(xml[]); -- array
of integers are supported with GIST CREATE
INDEX fx ON foo USING GIN((xpath('//id/text()',order_in_xml)::int[])); The xml data type is
unusual in that it does not provide any comparison operators. This is because there
is no well-defined and universally useful comparison algorithm for XML data.
One consequence of this is that you cannot retrieve rows by comparing an xml column against
a search value. XML values should therefore typically be accompanied by a
separate key field such as an ID. An alternative solution for comparing XML
values is to convert them to character strings first, but note that character
string comparison has little to do with a useful XML comparison method. Since there are
no comparison operators for the xml data type, it is not possible to create an index
directly on a column of this type. If speedy searches in XML data are desired,
possible workarounds include casting the _expression_ to a character string type
and indexing that, or indexing an XPath _expression_. Of course, the actual query
would have to be adjusted to search by the indexed _expression_. The text-search
functionality in PostgreSQL can also be used to speed up full-document searches
of XML data. The necessary preprocessing support is, however, not yet available
in the PostgreSQL distribution. |
