Re: [HACKERS] patch: function xmltable - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: [HACKERS] patch: function xmltable |
| Date | |
| Msg-id | CAFj8pRBrKhBaPcacv1_6xRPN=GTfQdu+co+YRZaF-9umOawaQg@mail.gmail.com Whole thread Raw |
| In response to | Re: [HACKERS] patch: function xmltable (Alvaro Herrera <alvherre@2ndquadrant.com>) |
| List | pgsql-hackers |
2017-03-03 21:04 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:
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.)
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@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
pgsql-hackers by date: