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

Pavel Stehule wrote:
> 2017-03-03 19:15 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

> > 2. As I've complained many times, I find the way we manage an empty
> > COLUMNS clause pretty bad.  The standard doesn't require that syntax
> > (COLUMNS is required), and I don't like the implementation, so why not
> > provide the feature in a different way?  My proposal is to change the
> > column options in gram.y to be something like this:
> 
> The clause COLUMNS is optional on Oracle and DB2
> 
> So I prefer a Oracle, DB2 design. If you are strongly against it, then we
> can remove it to be ANSI/SQL only.
> 
> I am don't see an good idea to introduce third syntax.

OK.  I think trying to be syntax compatible with DB2 or Oracle is a lost
cause, because the syntax used in the XPath expressions seems different
-- I think Oracle uses XQuery (which we don't support) and DB2 uses ...
not sure what it is, but it doesn't work in our implementation
(stuff like '$d/employees/emp' in the row expression.)

In existing applications using those Oracle/DB2, is it common to omit
the COLUMNS clause?  I searched for "xmltable oracle" and had a look at
the first few hits outside of the oracle docs:
http://viralpatel.net/blogs/oracle-xmltable-tutorial/
http://www.dba-oracle.com/t_xmltable.htm
http://stackoverflow.com/questions/12690868/how-to-use-xmltable-in-oracle
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9533111800346252295
http://stackoverflow.com/questions/1222570/what-is-an-xmltable
https://community.oracle.com/thread/3955198

Not a single one of these omit the COLUMNS clause (though the second one
mentions that the clause can be omitted).

I also looked at a few samples with DB2 -- same thing; it is possible,
but is it common?

Anyway, I noticed that "xml PATH '.'" can be used to obtain the full XML
of the row, which I think is the feature I wanted, so I think we're
covered and we can omit the case with no COLUMNS, since we already have
the feature in another way.  No need to implement anything further, and
we can rip out the special case I don't like.  Example:

CREATE TABLE EMPLOYEES
(      id     integer,      data   XML
);
INSERT INTO EMPLOYEES    VALUES (1, '<Employees>   <Employee emplid="1111" type="admin">
<firstname>John</firstname>      <lastname>Watson</lastname>       <age>30</age>       <email>johnwatson@sh.com</email>
 </Employee>   <Employee emplid="2222" type="admin">       <firstname>Sherlock</firstname>
<lastname>Homes</lastname>      <age>32</age>       <email>sherlock@sh.com</email>   </Employee>   <Employee
emplid="3333"type="user">       <firstname>Jim</firstname>       <lastname>Moriarty</lastname>       <age>52</age>
<email>jim@sh.com</email>   </Employee>   <Employee emplid="4444" type="user">       <firstname>Mycroft</firstname>
 <lastname>Holmes</lastname>       <age>41</age>       <email>mycroft@sh.com</email>   </Employee>
 
</Employees>');

This is with COLUMNS omitted:

alvherre=# select xmltable.* from employees, xmltable('/Employees/Employee' passing data);                xmltable
          
 
──────────────────────────────────────────<Employee emplid="1111" type="admin">   ↵        <firstname>John</firstname>
  ↵        <lastname>Watson</lastname>     ↵        <age>30</age>                   ↵
<email>johnwatson@sh.com</email>↵   </Employee><Employee emplid="2222" type="admin">   ↵
<firstname>Sherlock</firstname>↵        <lastname>Homes</lastname>      ↵        <age>32</age>                   ↵
 <email>sherlock@sh.com</email>  ↵    </Employee><Employee emplid="3333" type="user">    ↵
<firstname>Jim</firstname>     ↵        <lastname>Moriarty</lastname>   ↵        <age>52</age>                   ↵
 <email>jim@sh.com</email>       ↵    </Employee><Employee emplid="4444" type="user">    ↵
<firstname>Mycroft</firstname> ↵        <lastname>Holmes</lastname>     ↵        <age>41</age>                   ↵
 <email>mycroft@sh.com</email>   ↵    </Employee>
 

and this is what you get with "xml PATH '.'" (I threw in ORDINALITY just
for fun):

alvherre=# select xmltable.* from employees, xmltable('/Employees/Employee' passing data columns row_number for
ordinality,emp xml path '.');row_number │                   emp                    
 
────────────┼──────────────────────────────────────────         1 │ <Employee emplid="1111" type="admin">   ↵
│        <firstname>John</firstname>     ↵           │         <lastname>Watson</lastname>     ↵           │
<age>30</age>                  ↵           │         <email>johnwatson@sh.com</email>↵           │     </Employee>
  2 │ <Employee emplid="2222" type="admin">   ↵           │         <firstname>Sherlock</firstname> ↵           │
 <lastname>Homes</lastname>      ↵           │         <age>32</age>                   ↵           │
<email>sherlock@sh.com</email> ↵           │     </Employee>         3 │ <Employee emplid="3333" type="user">    ↵
    │         <firstname>Jim</firstname>      ↵           │         <lastname>Moriarty</lastname>   ↵           │
 <age>52</age>                   ↵           │         <email>jim@sh.com</email>       ↵           │     </Employee>
    4 │ <Employee emplid="4444" type="user">    ↵           │         <firstname>Mycroft</firstname>  ↵           │
   <lastname>Holmes</lastname>     ↵           │         <age>41</age>                   ↵           │
<email>mycroft@sh.com</email>  ↵           │     </Employee>
 

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