Grzegorz Jaśkiewicz <gryzman@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
