Re: patch: function xmltable

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>
<SIZE unit="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[(at)unit = "sq_km"]',
size_other text PATH
'concat(SIZE[(at)unit!="sq_km"], " ", SIZE[(at)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