future development of xml capabilities
am 26.05.2010 17:45:48 von Kasia Tuszynska --_000_232B5217AD58584C87019E8933556D1101FEEC0353redmx2esric om_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hello Postgres Gurus,
I am doing some research regarding the postgres native xml type, I found th=
at 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 exp=
ression 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 arra=
y 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 ope=
rators. This is because there is no well-defined and universally useful com=
parison 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 l=
ittle to do with a useful XML comparison method.
Since there are no comparison operators for the xml data type, it is not po=
ssible to create an index directly on a column of this type. If speedy sear=
ches in XML data are desired, possible workarounds include casting the expr=
ession to a character string type and indexing that, or indexing an XPath e=
xpression. 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 fu=
ll-document searches of XML data. The necessary preprocessing support is, h=
owever, not yet available in the PostgreSQL distribution.
--_000_232B5217AD58584C87019E8933556D1101FEEC0353redmx2esric om_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">
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 aroun=
ds
for it but, I was wondering if there were any plans to implement indexing o=
n a
xpath expression in future releases on Postges like Postgres 9?
/p>
Thank you,
Sncerely,
Kasia
------------------------------------------------------=
------------------------------------------------------------ ---------------=
--------------------8.3
has integrated xpath function. There is gap in XML support, because XML typ=
e
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:
ew"'>CREATE
OR REPLACE FUNCTION xml_list_to_int(xml[])
ew"'>RETURNS
int[] AS $$
ew"'>SELECT
ARRAY(SELECT to_number(($1[i])::text,'999999.99')::int
ew"'> &nbs=
p;
FROM generate_series(1, array_upper($1,1)) g(i))
ew"'>$$
LANGUAGE SQL IMMUTABLE;
ew"'>
ew"'>CREATE
CAST (xml[] AS int[]) WITH FUNCTION xml_list_to_int(xml[]);
ew"'>
ew"'>-- array
of integers are supported with GIST
ew"'>CREATE
INDEX fx ON foo USING
ew"'>GIN((xpath('//id/text()',order_in_xml)::int[]));
:auto'>
Accessing XML Values family:
"Times New Roman","serif"'>
:auto'>
is
unusual in that it does not provide any comparison operators. This is becau=
se 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 span>
ainst
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 charact=
er
string comparison has little to do with a useful XML comparison method.
:auto'>
e are
no comparison operators for the font-family:"Courier New"'>xml amily:
"Times New Roman","serif"'> data type, it is not possible to create an inde=
x
directly on a column of this type. If speedy searches in XML data are desir=
ed,
possible workarounds include casting the expression to a character string t=
ype
and indexing that, or indexing an XPath expression. Of course, the actual q=
uery
would have to be adjusted to search by the indexed expression.
/span>
:auto'>
earch
functionality in PostgreSQL can also be used to speed up full-document sear=
ches
of XML data. The necessary preprocessing support is, however, not yet avail=
able
in the PostgreSQL distribution.
--_000_232B5217AD58584C87019E8933556D1101FEEC0353redmx2esric om_--