From: | "Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: possible to create multivalued index from xpath() results in 8.3? |
Date: | 2007-11-21 03:19:15 |
Message-ID: | 51496.192.168.1.108.1195615155.squirrel@msqr.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
> AFAICT that's exactly what it does.
>
> regression=# select xpath('//foo[(at)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[(at)key="foo"]/text()', xml)))
);
-- m@
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-11-21 03:38:16 | Re: possible to create multivalued index from xpath() results in 8.3? |
Previous Message | Tom Lane | 2007-11-21 02:51:40 | Re: PostgreSQL is not behaving consistently across platforms |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-11-21 03:38:16 | Re: possible to create multivalued index from xpath() results in 8.3? |
Previous Message | Bruce Momjian | 2007-11-21 03:08:16 | Re: Fix pg_dump dependency on postgres.h |