Prev: Extraneous newlines in logfile from vacuumdb
Next: [HACKERS] pg_read_file() and non-ascii input file
From: Scott Bailey on 27 Oct 2009 18:27
Chris Graner wrote:
> Hello,
>
> I've been reading over the documentation to find an alternative to the
> deprecated xpath_table functionality. I think it may be a possibility
> but I'm not seeing a clear alternative.
>
> Thanks,
>
> Chris Graner

The standard is XMLTABLE and is implemented by both db2 and oracle but
is on our list of unimplemented features. I would love to see this
implemented in Postgres. I recall it coming up here before. But I don't
think it went beyond discussing which xquery library we could use.

Scott Bailey

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Andrew Dunstan on 13 Nov 2009 22:05


Scott Bailey wrote:
> Chris Graner wrote:
>> Hello,
>>
>> I've been reading over the documentation to find an alternative to
>> the deprecated xpath_table functionality. I think it may be a
>> possibility but I'm not seeing a clear alternative.
>>
>> Thanks,
>>
>> Chris Graner
>
> The standard is XMLTABLE and is implemented by both db2 and oracle but
> is on our list of unimplemented features. I would love to see this
> implemented in Postgres. I recall it coming up here before. But I
> don't think it went beyond discussing which xquery library we could use.
>
>

Yes, Chris spoke to me about this last night and emailed me an example
of what he needs today, and I've spent the couple of hours thinking
about it. Not have a nice way of getting a recordset out of a piece of
XML is actually quite a gap in our API.

The trouble is that XMLTABLE is a horrible grammatical mess, ISTM, and I
don't much like the way xpath_table() works either. Passing a table name
as text into a function is rather ugly.

I think we could do with a much simple, albeit non-standard, API.
Something like:

xpathtable(source xml, rootnodes text, leaves variadic text[])
returns setof record

But unless I'm mistaken we'd need the proposed LATERAL extension to make
it iterate nicely over a table. Then we could possibly do something like:

select x.bar, x.blurfl
from
foo f,
lateral
xpathtable(f.xmlfield,'//foo','bar','baz[1]/blurfl','@is-some-property')
as x(bar int, blurfl text, xmlprop bool)
where f.otherfield or x.xmlprop;

cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Scott Bailey on 19 Nov 2009 00:03
Andrew Dunstan wrote:
>

>>> I've been reading over the documentation to find an alternative to
>>> the deprecated xpath_table functionality. I think it may be a
>>> possibility but I'm not seeing a clear alternative.
>>>
>>> Thanks,
>>>
>>> Chris Graner
>>
>> The standard is XMLTABLE and is implemented by both db2 and oracle but
>> is on our list of unimplemented features. I would love to see this
>> implemented in Postgres. I recall it coming up here before. But I
>> don't think it went beyond discussing which xquery library we could use.
>>
>>
>
> Yes, Chris spoke to me about this last night and emailed me an example
> of what he needs today, and I've spent the couple of hours thinking
> about it. Not have a nice way of getting a recordset out of a piece of
> XML is actually quite a gap in our API.
>
> The trouble is that XMLTABLE is a horrible grammatical mess, ISTM, and I
> don't much like the way xpath_table() works either. Passing a table name
> as text into a function is rather ugly.
>
> I think we could do with a much simple, albeit non-standard, API.
> Something like:
>
> xpathtable(source xml, rootnodes text, leaves variadic text[])
> returns setof record
>
> But unless I'm mistaken we'd need the proposed LATERAL extension to make
> it iterate nicely over a table. Then we could possibly do something like:
>
> select x.bar, x.blurfl
> from
> foo f,
> lateral
> xpathtable(f.xmlfield,'//foo','bar','baz[1]/blurfl','@is-some-property')
> as x(bar int, blurfl text, xmlprop bool)
> where f.otherfield or x.xmlprop;
>
> cheers
>
> andrew

I agree that the syntax of XMLTABLE is odd. But not demonstrably worse
than xpathtable. If we are going to exert effort on it, why not do it in
a standards compliant way? Otherwise I'd suggest a stop gap of just
adding some support functions to make it easier to extract a scalar
value from a node. Something like what I did here.

http://scottrbailey.wordpress.com/2009/06/19/xml-parsing-postgres/

The nice thing about XMLTABLE is that it adds xquery support. I think
the majority of xquery engines seem to be written in Java. XQuilla is
C++. I'm not sure if our licensing is compatible, but it I would love
the irony of using Berkeley DB XML (formerly Sleepycat) now that its
owned by Oracle.

Scott

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Andrew Dunstan on 19 Nov 2009 09:17


Scott Bailey wrote:
>
> I agree that the syntax of XMLTABLE is odd. But not demonstrably worse
> than xpathtable.

That's not saying much. I dislike both. Why the SQL committee feels the
need to invent arcane special case grammar rules is beyond me. I
understand why the author of xpathtable designed it the way he did, but
it's still ugly in my book.

As I said, with LATERAL we could produce a much cleaner functional
equivalent.

> If we are going to exert effort on it, why not do it in a standards
> compliant way? Otherwise I'd suggest a stop gap of just adding some
> support functions to make it easier to extract a scalar value from a
> node. Something like what I did here.
>
> http://scottrbailey.wordpress.com/2009/06/19/xml-parsing-postgres/

I think that's an orthogonal issue, really. There's probably a good case
for such a function whether or not we do something like xpath_table.

>
> The nice thing about XMLTABLE is that it adds xquery support. I think
> the majority of xquery engines seem to be written in Java. XQuilla is
> C++. I'm not sure if our licensing is compatible, but it I would love
> the irony of using Berkeley DB XML (formerly Sleepycat) now that its
> owned by Oracle.
>
>

XQuery is a whole other question. Adding another library dependency is
something we try to avoid. Zorba <http://www.zorba-xquery.com/> might
work, but it appears to have its own impressive list of dependencies
(why does it require both libxml2 and xerces-c? That looks a bit redundant.)

Even if we did implement XMLTABLE, I think I'd probably be inclined to
start by limiting it to plain XPath, without the FLWOR stuff. I think
that would satisfy the vast majority of needs, although you might feel
differently. (Do a Google for XMLTABLE - every example I found uses
plain XPath expressions.)

cheers

andrew




--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Robert Haas on 19 Nov 2009 09:53
On Thu, Nov 19, 2009 at 12:03 AM, Scott Bailey <artacus(a)comcast.net> wrote:
> The nice thing about XMLTABLE is that it adds xquery support. I think the
> majority of xquery engines seem to be written in Java. XQuilla is C++. I'm
> not sure if our licensing is compatible, but it I would love the irony of
> using Berkeley DB XML (formerly Sleepycat) now that its owned by Oracle.

It's very much not compatible. Berkeley DB is not free for commercial
use. I anticipate that this would be a problem both for commericial
users of PostgreSQL and also for commercial PostgreSQL forks.
Besides, that's a lot of code to suck into Postgres to do, uh, a lot
of things that we already do in other ways.

....Robert

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 |  Next  |  Last
Pages: 1 2
Prev: Extraneous newlines in logfile from vacuumdb
Next: [HACKERS] pg_read_file() and non-ascii input file