Re: [HACKERS] patch: function xmltable - Mailing list pgsql-hackers

From Alvaro Herrera
Subject Re: [HACKERS] patch: function xmltable
Date
Msg-id 20170302183205.hgz3ekvwbfnzcymb@alvherre.pgsql
Whole thread Raw
In response to Re: [HACKERS] patch: function xmltable  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: [HACKERS] patch: function xmltable  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-hackers
So in the old (non-executor-node) implementation, you could attach WITH
ORDINALITY to the xmltable expression and it would count the output
rows, regardless of which XML document it comes from.  With the new
implementation, the grammar no longer accepts it.  To count output rows,
you still need to use row_number().  Maybe this is okay.  This is the
example from the docs, and I add another XML document with two more rows
for xmltable.  Look at the three numbering columns ...

CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>                       <ROW id="1">   <COUNTRY_ID>AU</COUNTRY_ID>   <COUNTRY_NAME>Australia</COUNTRY_NAME> </ROW>
                                   <ROW id="5">
<COUNTRY_ID>JP</COUNTRY_ID>  <COUNTRY_NAME>Japan</COUNTRY_NAME>   <PREMIER_NAME>Shinzo Abe</PREMIER_NAME>   <SIZE
unit="sq_mi">145935</SIZE></ROW> <ROW id="6">   <COUNTRY_ID>SG</COUNTRY_ID>   <COUNTRY_NAME>Singapore</COUNTRY_NAME>
<SIZEunit="sq_km">697</SIZE> </ROW>
 
</ROWS>
$$ AS data;
insert into xmldata values ($$<ROWS><ROW id="2"><COUNTRY_ID>CL</COUNTRY_ID><COUNTRY_NAME>Chile</COUNTRY_NAME></ROW><ROW
id="3"><COUNTRY_ID>AR</COUNTRY_ID><COUNTRY_NAME>Argentina</COUNTRY_NAME></ROW></ROWS>$$);

SELECT ROW_NUMBER() OVER (), xmltable.* FROM xmldata,
        XMLTABLE('//ROWS/ROW'                                                                                 PASSING
data              COLUMNS id int PATH '@id',                       ordinality FOR ORDINALITY,
"COUNTRY_NAME"text,                       country_id text PATH 'COUNTRY_ID',                       size_sq_km float
PATH'SIZE[@unit = "sq_km"]',                       size_other text PATH
'concat(SIZE[@unit!="sq_km"]," ", SIZE[@unit!="sq_km"]/@unit)',                       premier_name text PATH
'PREMIER_NAME'DEFAULT 'not specified')
 
;
row_number │ id │ ordinality │ COUNTRY_NAME │ country_id │ size_sq_km │  size_other  │ premier_name  
────────────┼────┼────────────┼──────────────┼────────────┼────────────┼──────────────┼───────────────         1 │  1 │
        1 │ Australia    │ AU         │            │              │ not specified         2 │  5 │          2 │ Japan
    │ JP         │            │ 145935 sq_mi │ Shinzo Abe         3 │  6 │          3 │ Singapore    │ SG         │
  697 │              │ not specified         4 │  2 │          1 │ Chile        │ CL         │            │
│ not specified         5 │  3 │          2 │ Argentina    │ AR         │            │              │ not specified
 


-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] I propose killing PL/Tcl's "modules" infrastructure
Next
From: Pavel Stehule
Date:
Subject: Re: [HACKERS] patch: function xmltable