Re: patch: function xmltable

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