Re: patch: function xmltable

2017-03-02 19:32 GMT+01:00 Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:

> 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 ...
>

It is expected - now tablefunc are not special case of SRF, so it lost all
SRF functionality. It is not critical lost - it supports internally FOR
ORDINALITY column, and classic ROW_NUMBER can be used. It can be enhanced
to support WITH ORDINALITY in future, but I have not any use case for it.

Regards

Pavel

>
> 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
>