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[]));
8.13.3. Accessing XML Values
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.
