Re: patch: function xmltable

2017-03-03 21:04 GMT+01:00 Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:

> Pavel Stehule wrote:
> > 2017-03-03 19:15 GMT+01:00 Alvaro Herrera <alvherre(at)2ndquadrant(dot)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>johnwatson(at)sh(dot)com</email>
> </Employee>
> <Employee emplid="2222" type="admin">
> <firstname>Sherlock</firstname>
> <lastname>Homes</lastname>
> <age>32</age>
> <email>sherlock(at)sh(dot)com</email>
> </Employee>
> <Employee emplid="3333" type="user">
> <firstname>Jim</firstname>
> <lastname>Moriarty</lastname>
> <age>52</age>
> <email>jim(at)sh(dot)com</email>
> </Employee>
> <Employee emplid="4444" type="user">
> <firstname>Mycroft</firstname>
> <lastname>Holmes</lastname>
> <age>41</age>
> <email>mycroft(at)sh(dot)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(at)sh(dot)com</email>↵
> </Employee>
> <Employee emplid="2222" type="admin"> ↵
> <firstname>Sherlock</firstname> ↵
> <lastname>Homes</lastname> ↵
> <age>32</age> ↵
> <email>sherlock(at)sh(dot)com</email> ↵
> </Employee>
> <Employee emplid="3333" type="user"> ↵
> <firstname>Jim</firstname> ↵
> <lastname>Moriarty</lastname> ↵
> <age>52</age> ↵
> <email>jim(at)sh(dot)com</email> ↵
> </Employee>
> <Employee emplid="4444" type="user"> ↵
> <firstname>Mycroft</firstname> ↵
> <lastname>Holmes</lastname> ↵
> <age>41</age> ↵
> <email>mycroft(at)sh(dot)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(at)sh(dot)com</email>↵
> │ </Employee>
> 2 │ <Employee emplid="2222" type="admin"> ↵
> │ <firstname>Sherlock</firstname> ↵
> │ <lastname>Homes</lastname> ↵
> │ <age>32</age> ↵
> │ <email>sherlock(at)sh(dot)com</email> ↵
> │ </Employee>
> 3 │ <Employee emplid="3333" type="user"> ↵
> │ <firstname>Jim</firstname> ↵
> │ <lastname>Moriarty</lastname> ↵
> │ <age>52</age> ↵
> │ <email>jim(at)sh(dot)com</email> ↵
> │ </Employee>
> 4 │ <Employee emplid="4444" type="user"> ↵
> │ <firstname>Mycroft</firstname> ↵
> │ <lastname>Holmes</lastname> ↵
> │ <age>41</age> ↵
> │ <email>mycroft(at)sh(dot)com</email> ↵
> │ </Employee>
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>