> AFAICT that's exactly what it does.
>
> regression=# select xpath('//foo[@key="mykey"]/text()', '<value>ABC<foo
> key="mykey">XYZ</foo></value><foo key="mykey">RST</foo><foo>DEF</foo>');
> xpath
> -----------
> {XYZ,RST}
> (1 row)
>
> regression=#
>
> Of course this is of type xml[], but you can cast to text[] and then
> index.
Ugh, you're right of course! Somehow I had this wrong. So I tried to
create an index on the xml[] result by casting to text[] but I got the
"function must be immutable" error. Is there any reason the xml[] to
text[] cast is not immutable?
I worked around it by writing a function like
CREATE OR REPLACE FUNCTION xpath_to_text(xml_array xml[]) RETURNS text[] AS
$BODY$
BEGIN
RETURN xml_array::text[];
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
and wrapping my CREATE INDEX call with that, like:
create index type_flag_idx on lead using gin (
(xpath_to_text(xpath('/element[@key="foo"]/text()', xml)))
);
-- m@
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
