On Sep 25, 2007 10:57 AM, Pavel Stehule <
pavel.stehule@gmail.com> wrote:
Hello
Current result from xpath function isn't indexable. It cannot be
problem with possibility cast it to some base types.
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[]);
-- now I can build functional index
CREATE INDEX fx ON foo USING
GIN((xpath('//id/text()',objednavka_v_xml)::int[]));
Does anybody know better solution?
Alternative (and maybe better) approach would be:
- create comparison functions that work in the same way as string comparison functions do (currently, it's straight forward since XML is stored as string);
- do NOT create comparison operators to avoid explicit comparing XML values (to follow standard ways to work with XML and to avoid possible unexpected behaviors);
- create opclass based on these functions and, therefore, obtain GIN indexes support for xml[];
- describe in the docs, that one can use GIN indexes over XPath expressions, but should be aware that comparison with non-trivial XML constants have to be used carefully because of possible problems with whitespaces, etc (in other words, comparison here is doing letter by letter, as for varchar).
If there are no objections I'll send patch for this.
