Re: [HACKERS] patch: function xmltable - Mailing list pgsql-hackers

I've been giving this a look.  I started by tweaking the docs once
again, and while verifying that the example works as expected, I
replayed what I have in sgml:

... begin SGML paste ...
    <para>
     For example, given the following XML document:
  <screen><![CDATA[
<ROWS>
  <ROW id="1">
    <COUNTRY_ID>AU</COUNTRY_ID>
    <COUNTRY_NAME>Australia</COUNTRY_NAME>
  </ROW>
  <ROW id="5">
    <COUNTRY_ID>JP</COUNTRY_ID>
    <COUNTRY_NAME>Japan</COUNTRY_NAME>
    <PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
  </ROW>
  <ROW id="6">
    <COUNTRY_ID>SG</COUNTRY_ID>
    <COUNTRY_NAME>Singapore</COUNTRY_NAME>
    <SIZE unit="km">791</SIZE>
  </ROW>
</ROWS>
]]></screen>

     the following query produces the result shown below:

<screen><![CDATA[
SELECT  xmltable.*
  FROM (SELECT data FROM xmldata) x,
        LATERAL xmltable('//ROWS/ROW'
                         PASSING data
                         COLUMNS id int PATH '@id',
                                 ordinality FOR ORDINALITY,
                                 country_name text PATH 'COUNTRY_NAME',
                                 country_id text PATH 'COUNTRY_ID',
                                 size float PATH 'SIZE[@unit = "km"]/text()',
                                 unit text PATH 'SIZE/@unit',
                                 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
... end SGML paste ...


But the query doesn't actually return a table, but instead it fails with
this error:
  ERROR:  invalid input syntax for type double precision: ""
This is because of the "size" column (if I remove SIZE from the COLUMNS
clause, the query returns correctly).  Apparently, for the rows where
SIZE is not given, we try to inssert an empty string instead of a NULL
value, which is what I expected.

I'm using your v44 code, but trimmed both the XML document used in SGML
as well as modified the query slightly to show additional features.  But
those changes should not cause the above error ...

  FROM (SELECT data FROM xmldata) x,       LATERAL xmltable('//ROWS/ROW'                        PASSING data                        COLUMNS id int PATH '@id',                                ordinality FOR ORDINALITY,                                country_name text PATH 'COUNTRY_NAME',                                country_id text PATH 'COUNTRY_ID',                                size float PATH 'SIZE[@unit = "km"]',                                unit text PATH 'SIZE/@unit',                                premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services