Re: Current state of XML capabilities in PostgreSQL?

Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com> wrote:

>> why would you store data thats wrapped in two copies of its fieldname along
>> with other punctuation?    wouldn't it make more sense to decompose your XML
>> source into proper tables so proper indexes and relational sql queries can
>> be made?     otherwise, every query turns into a massive sequential scan and
>> parsing operation.

> you can always have index on xpath() ...
> and than use same expression in WHERE, and postgresql will use index.

Interesting. I had thought that it was not possible to index
on XML columns because no comparison operators were defined:

| tim=# CREATE TEMPORARY TABLE tmpXML (x XML PRIMARY KEY);
| FEHLER: Datentyp xml hat keine Standardoperatorklasse für Zugriffsmethode »btree«
| HINT: Sie müssen für den Index eine Operatorklasse angeben oder eine Standardoperatorklasse für den Datentyp definieren.

yet an array of XML works:

| tim=# CREATE TEMPORARY TABLE tmpXML (x XML[] PRIMARY KEY);
| HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »tmpxml_pkey« für Tabelle »tmpxml«
| CREATE TABLE
| tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')));
| INSERT 0 1

though only once:

| tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual 2</title><chapter>...</chapter></book>')));
| FEHLER: konnte keine Vergleichsfunktion für Typ xml ermitteln

So an index on xpath() should not be possible.

Tim