I have pushed the patch now (in your original form rather than my later
formulation) -- let's see how the buildfarm likes it. There are (at
least) three issues remaining, as per below; Pavel, do you have any
insight on these?
First one is about array indexes not working sanely (I couldn't get this
to work in Oracle)
> > Also, array indexes behave funny. First let's add more XML comments
> > inside that number, and query for the subscripts:
> >
> > update xmldata set data = regexp_replace(data::text, '7<!--small
> > country-->91', '<!--ah-->7<!--oh-->9<!--uh-->1')::xml;
> >
> > SELECT xmltable.*
> > FROM (SELECT data FROM xmldata) x,
> > LATERAL XMLTABLE('/ROWS/ROW'
> > PASSING data
> > COLUMNS
> > country_name text PATH
> > 'COUNTRY_NAME/text()' NOT NULL,
> > size_text float PATH 'SIZE/text()',
> > size_text_1 float PATH 'SIZE/text()[1]',
> > size_text_2 float PATH 'SIZE/text()[2]',
> > "SIZE" float, size_xml xml PATH 'SIZE')
> > where size_text is not null;
> >
> > country_name │ size_text │ size_text_1 │ size_text_2 │ size_text_3 │ SIZE │
> > size_xml
> > ──────────────┼───────────┼─────────────┼─────────────┼─────────────┼──────┼───────────────────────────────────────────────────────
> > Singapore │ 791 │ 791 │ 91 │ 1 │ 791 │
> > <SIZE unit="km"><!--ah-->7<!--oh-->9<!--uh-->1</SIZE>
> > (1 fila)
The second one is about (lack of!) processing instructions and comments:
> Also, node() matching comments or processing instructions
> seems to be broken too:
>
> SELECT *
> FROM (VALUES ('<xml><!--comment--></xml>'::xml)
> , ('<xml><?pi content?></xml>'::xml)
> ) d(x)
> CROSS JOIN LATERAL
> XMLTABLE('/xml'
> PASSING x
> COLUMNS "node()" TEXT PATH 'node()'
> ) t
>
> x | node()
> ---------------------------+--------
> <xml><!--comment--></xml> |
> <xml><?pi content?></xml> |
> (2 rows)
>
> I can look into this, but it may take a while.
Compare the empty second columns with oracle behavior, which returns the
contents of the PI and the comment. As a script for
http://rextester.com/l/oracle_online_compiler
create table xmltb (data xmltype) \\
insert into xmltb values ('<xml><!--the comment is here--></xml>') \\
insert into xmltb values ('<xml><?pi php_stuff(); do_stuff("hello"); ?></xml>')
\\
SELECT * FROM xmltb, XMLTABLE('/xml' PASSING data COLUMNS node varchar2(100)
PATH 'node()') t \\
drop table xmltb \\
The third issue is the way we output comments when they're in a column
of type XML:
> > Note what happens if I change the type from text to xml in that
> > column:
> >
> > SELECT *
> > FROM (VALUES ('<xml>te<!-- ahoy -->xt</xml>'::xml)
> > , ('<xml><![CDATA[some <!-- really --> weird
> > <stuff>]]></xml>'::xml)
> > ) d(x)
> > CROSS JOIN LATERAL
> > XMLTABLE('/xml'
> > PASSING x
> > COLUMNS "node()" xml PATH 'node()'
> > ) t;
> >
> > x │
> > node()
> > ───────────────────────────────────────────────────────────┼────────────────────────────────────────────────
> > <xml>te<!-- ahoy -->xt</xml> │ te ahoy xt
> > <xml><![CDATA[some <!-- really --> weird <stuff>]]></xml> │ some <!--
> > really --> weird <stuff>
> > (2 filas)
>
> The comment seems to be wrong.
>
> I guess it’s fine if the CDATA gets transformed in to an equivalent
> string using the XML entities. Yet, it might be better avoiding it.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
