Re: patch: function xmltable

2017-03-02 8:04 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
> 2017-03-02 1:12 GMT+01:00 Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:
>
>>
>> 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[(at)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 ...
>>
>
> The example in doc is obsolete. Following example works without problems.
>
> 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[(at)unit = "km"]',
> unit text PATH 'SIZE/@unit',
> premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
>
>
> It is related to older variants of this patch, where I explicitly mapped
> empty strings to NULL.
>
> Now, I don't do it - I use libxml2 result with following mapping
>
> No tag ... NULL
> empty tag ... empty string
>
> Important question is about mapping empty tags to Postgres. I prefer
> current behave, because I have a possibility to differ between these states
> on application level. If we returns NULL for empty tag, then there will not
> be possible detect if XML has tag (although empty) or not. The change is
> simple - just one row - but I am thinking so current behave is better.
> There is possible risk of using /text() somewhere - it enforce a empty tag
> with all negative impacts.
>
> I prefer to fix doc in conformance with regress tests and append note
> about mapping these corner cases from XML to relations.
>
> What do you think about it?
>

It is documented already

"If the <literal>PATH</> matches an empty tag the result is an empty string"

Attached new patch

cleaned documentation
regress tests is more robust
appended comment in src related to generating empty string for empty tag

Regards

Pavel

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