2017-03-03 21:04 GMT+01:00 Alvaro Herrera <alvhe...@2ndquadrant.com>:
> Pavel Stehule wrote: > > 2017-03-03 19:15 GMT+01:00 Alvaro Herrera <alvhe...@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.) > 100% compatibility is not possible - but XPath is subset of XQuery and in reality - the full XQuery examples of XMLTABLE is not often. Almost all examples of usage XMLTABLE, what I found in blogs, uses XPath only > > 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? > I don't think so it is common - it is corner case - and I can live without it well > > 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: > yes, > > 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>johnwat...@sh.com</email> > </Employee> > <Employee emplid="2222" type="admin"> > <firstname>Sherlock</firstname> > <lastname>Homes</lastname> > <age>32</age> > <email>sherl...@sh.com</email> > </Employee> > <Employee emplid="3333" type="user"> > <firstname>Jim</firstname> > <lastname>Moriarty</lastname> > <age>52</age> > <email>j...@sh.com</email> > </Employee> > <Employee emplid="4444" type="user"> > <firstname>Mycroft</firstname> > <lastname>Holmes</lastname> > <age>41</age> > <email>mycr...@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>johnwat...@sh.com</email>↵ > </Employee> > <Employee emplid="2222" type="admin"> ↵ > <firstname>Sherlock</firstname> ↵ > <lastname>Homes</lastname> ↵ > <age>32</age> ↵ > <email>sherl...@sh.com</email> ↵ > </Employee> > <Employee emplid="3333" type="user"> ↵ > <firstname>Jim</firstname> ↵ > <lastname>Moriarty</lastname> ↵ > <age>52</age> ↵ > <email>j...@sh.com</email> ↵ > </Employee> > <Employee emplid="4444" type="user"> ↵ > <firstname>Mycroft</firstname> ↵ > <lastname>Holmes</lastname> ↵ > <age>41</age> ↵ > <email>mycr...@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>johnwat...@sh.com</email>↵ > │ </Employee> > 2 │ <Employee emplid="2222" type="admin"> ↵ > │ <firstname>Sherlock</firstname> ↵ > │ <lastname>Homes</lastname> ↵ > │ <age>32</age> ↵ > │ <email>sherl...@sh.com</email> ↵ > │ </Employee> > 3 │ <Employee emplid="3333" type="user"> ↵ > │ <firstname>Jim</firstname> ↵ > │ <lastname>Moriarty</lastname> ↵ > │ <age>52</age> ↵ > │ <email>j...@sh.com</email> ↵ > │ </Employee> > 4 │ <Employee emplid="4444" type="user"> ↵ > │ <firstname>Mycroft</firstname> ↵ > │ <lastname>Holmes</lastname> ↵ > │ <age>41</age> ↵ > │ <email>mycr...@sh.com</email> ↵ > │ </Employee> > > -- > Álvaro Herrera https://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
