Re: patch: function xmltable - Mailing list pgsql-hackers
| From | Alvaro Herrera |
|---|---|
| Subject | Re: patch: function xmltable |
| Date | |
| Msg-id | 20161124032620.ejd3q74z4gxonymh@alvherre.pgsql Whole thread Raw |
| In response to | Re: patch: function xmltable (Alvaro Herrera <alvherre@2ndquadrant.com>) |
| List | pgsql-hackers |
Alvaro Herrera wrote:
> If you use "PATH '/'" for a column, you get the text for all the entries
> in the whole XML, rather than the text for the particular row being
> processed. Isn't that rather weird, or to put it differently, completely
> wrong? I didn't find a way to obtain the whole XML row when you have
> the COLUMNS option (which is what I was hoping for with the "PATH '/'").
Ah, apparently you need to use type XML for that column in order for
this to happen. Example:
insert into emp values ($$
<depts ><dept bldg="102"> <employee id="905"> <name> <first>John</first>
<last>Doew</last> </name> <office>344</office>
<salary currency="USD">55000</salary>
</employee>
<employee id="908"> <name> <first>Peter</first> <last>Panw</last> </name>
<office>216</office> <phone>905-416-5004</phone> </employee></dept>
<dept bldg="115"> <employee id="909"> <name> <first>Mary</first> <last>Jonesw</last>
</name> <office>415</office> <phone>905-403-6112</phone> <phone>647-504-4546</phone>
<salarycurrency="USD">64000</salary> </employee></dept>
</depts>
$$);
Note the weird salary_amount value here:
SELECT x.*
FROM emp,
XMLTABLE ('//depts/dept/employee' passing doc COLUMNS i for ordinality, empID int PATH '@id',
firstname varchar(25) PATH 'name/first' default 'FOOBAR', lastname VARCHAR(25) PATH
'name/last', salary xml path 'concat(salary/text(), salary/@currency)' default 'DONT KNOW', salary_amount xml path
'/') WITH ORDINALITY AS X (i, a, b, c) limit 1;i │ a │ b │ c │ salary │ salary_amount │ ordinality
───┼─────┼──────┼──────┼──────────┼───────────────────────┼────────────1 │ 905 │ John │ Doew │ 55000USD │
↵│ 1 │ │ │ │ │ ↵│ │ │ │ │ │
↵│ │ │ │ │ │ John ↵│ │ │ │ │ │
Doew ↵│ │ │ │ │ │ ↵│ │ │ │ │ │ 344
↵│ │ │ │ │ │ 55000 ↵│ │ │ │ │ │
↵│ │ │ │ │ │ ↵│ │ │ │ │ │
↵│ │ │ │ │ │ ↵│ │ │ │ │ │ Peter
↵│ │ │ │ │ │ Panw ↵│ │ │ │ │ │ ↵│
│ │ │ │ │ 216 ↵│ │ │ │ │ │ 905-416-5004↵│
│ │ │ │ │ ↵│ │ │ │ │ │ ↵│ │
│ │ │ │ ↵│ │ │ │ │ │ ↵│ │
│ │ │ │ ↵│ │ │ │ │ │ ↵│ │ │
│ │ │ Mary ↵│ │ │ │ │ │ Jonesw ↵│ │ │
│ │ │ ↵│ │ │ │ │ │ 415 ↵│ │ │ │
│ │ 905-403-6112↵│ │ │ │ │ │ 647-504-4546↵│ │ │ │
│ │ 64000 ↵│ │ │ │ │ │ ↵│ │ │ │
│ │ ↵│ │ │ │ │ │ │
(1 fila)
If you declare salary_amount to be text instead, it doesn't happen anymore.
Apparently if you put it in a namespace, it doesn't hapen either.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgsql-hackers by date: