Обсуждение: xpath() subquery for empty array
In postgres 8.4 When running xpath() queries it seems that empty results are always returned. So if I query a table containing 1000 XML documents a 1000 rows will always be fetched even if the xpath() element of the query only matches 10 documents.
The documentation states:
Thanks in advance,
Roy
The documentation states:
The functionYet it seems that empty node sets (arrays) are also returned. So, my thought was to run a subquery to eliminate empty arrays, e.g.:xpath
evaluates the XPath expression xpath against the XML value xml. It returns an array of XML values corresponding to the node set produced by the XPath expression.
SELECT xHow do I test for an empty array in postgres?
FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml)
AS x FROM docs) AS y WHERE x <> [test for empty array?????????]
Thanks in advance,
Roy
Roy Walter wrote: > In postgres 8.4 When running xpath() queries it seems that empty results > are always returned. So if I query a table containing 1000 XML documents > a 1000 rows will always be fetched even if the xpath() element of the > query only matches 10 documents. > > The documentation states: > > The function |xpath| evaluates the XPath expression /xpath/ against > the XML value /xml/. It returns an array of XML values corresponding > to the node set produced by the XPath expression. > > Yet it seems that empty node sets (arrays) are also returned. So, my > thought was to run a subquery to eliminate empty arrays, e.g.: > > SELECT x > FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) > AS x FROM docs) AS y WHERE x <> [test for empty array?????????] > > How do I test for an empty array in postgres? > > Thanks in advance, > Roy WHERE x != array[]::xml[]
Scott Bailey wrote:
Roy Walter wrote:Thanks Scott but that throws up a syntax error (at the closing bracket of array[]):In postgres 8.4 When running xpath() queries it seems that empty results are always returned. So if I query a table containing 1000 XML documents a 1000 rows will always be fetched even if the xpath() element of the query only matches 10 documents.
The documentation states:
The function |xpath| evaluates the XPath expression /xpath/ against
the XML value /xml/. It returns an array of XML values corresponding
to the node set produced by the XPath expression.
Yet it seems that empty node sets (arrays) are also returned. So, my thought was to run a subquery to eliminate empty arrays, e.g.:
SELECT x
FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml)
AS x FROM docs) AS y WHERE x <> [test for empty array?????????]
How do I test for an empty array in postgres?
Thanks in advance,
Roy
WHERE x != array[]::xml[]
No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.387 / Virus Database: 270.13.12/2233 - Release Date: 07/12/09 08:20:00
ERROR: syntax error at or near "]"-- Roy
LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[]
^
On Sun, Jul 12, 2009 at 06:41:57PM +0100, Roy Walter wrote: > Scott Bailey wrote: > >Roy Walter wrote: > >>How do I test for an empty array in postgres? > > > >WHERE x != array[]::xml[] > > > > > Thanks Scott but that throws up a syntax error (at the closing bracket > of array[]): > > ERROR: syntax error at or near "]" > LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[] Even if that syntax was correct it wouldn't work, xml values don't have an equality operator defined for them. I've normally tested the array size to figure out when they're empty, something like: array_upper($1,1) > 0 However, I've just noticed that this returns NULL rather than zero as I was expecting for an empty array (i.e. the literal '{}'). It also doesn't seem to do useful things if you're using unusual bounds on your array. Bah, the semantics of arrays in PG always seem over-complicated to me! -- Sam http://samason.me.uk/
Sam Mason wrote: > On Sun, Jul 12, 2009 at 06:41:57PM +0100, Roy Walter wrote: >> Scott Bailey wrote: >>> Roy Walter wrote: >>>> How do I test for an empty array in postgres? >>> WHERE x != array[]::xml[] >>> >>> >> Thanks Scott but that throws up a syntax error (at the closing bracket >> of array[]): >> >> ERROR: syntax error at or near "]" >> LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[] > > Even if that syntax was correct it wouldn't work, xml values don't have > an equality operator defined for them. I've normally tested the array > size to figure out when they're empty, something like: > > array_upper($1,1) > 0 > > However, I've just noticed that this returns NULL rather than zero as > I was expecting for an empty array (i.e. the literal '{}'). It also > doesn't seem to do useful things if you're using unusual bounds on your > array. > > Bah, the semantics of arrays in PG always seem over-complicated to me! This worked on both 8.3 and 8.4 SELECT * FROM ( VALUES( '{}'::_xml),('{<root/>}'::_xml) ) sub WHERE array_upper(column1, 1) > 0Sam Mason <sam@samason.me.uk> And as Sam noted, array_upper needs to return null if the array is empty because 0 and -1 can be valid indexes for arrays in postgres.
Scott Bailey wrote:
Sam Mason wrote:Thanks, that's great, I'm delighted that there's a solution. Where exactly does that fit in terms of my original query, i.e.:On Sun, Jul 12, 2009 at 06:41:57PM +0100, Roy Walter wrote:Scott Bailey wrote:Roy Walter wrote:Thanks Scott but that throws up a syntax error (at the closing bracket of array[]):How do I test for an empty array in postgres?WHERE x != array[]::xml[]
ERROR: syntax error at or near "]"
LINE 3: AS x FROM docs) AS y WHERE x != array[]::xml[]
Even if that syntax was correct it wouldn't work, xml values don't have
an equality operator defined for them. I've normally tested the array
size to figure out when they're empty, something like:
array_upper($1,1) > 0
However, I've just noticed that this returns NULL rather than zero as
I was expecting for an empty array (i.e. the literal '{}'). It also
doesn't seem to do useful things if you're using unusual bounds on your
array.
Bah, the semantics of arrays in PG always seem over-complicated to me!
This worked on both 8.3 and 8.4
SELECT *
FROM (
VALUES( '{}'::_xml),('{<root/>}'::_xml)
) sub
WHERE array_upper(column1, 1) > 0Sam Mason <sam@samason.me.uk>
And as Sam noted, array_upper needs to return null if the array is empty because 0 and -1 can be valid indexes for arrays in postgres.
SELECT x-- Roy
FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml)
AS x FROM docs) AS y WHERE x <> [test for empty array?????????]
On Sun, Jul 12, 2009 at 09:49:15PM +0100, Roy Walter wrote: > Where exactly does that fit in terms of my original query, i.e.: > > SELECT x > FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) > AS x FROM docs) AS y WHERE x <> [test for empty array?????????] Something like the following should do the right thing: SELECT x FROM ( SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE array_upper(x, 1) > 0; -- Sam http://samason.me.uk/
Sam Mason wrote:
-- Roy
Thank you, works just fine. I thought it was more complicated than that :-)On Sun, Jul 12, 2009 at 09:49:15PM +0100, Roy Walter wrote:Where exactly does that fit in terms of my original query, i.e.: SELECT x FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE x <> [test for empty array?????????]Something like the following should do the right thing: SELECT x FROM ( SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml) AS x FROM docs) AS y WHERE array_upper(x, 1) > 0;
-- Roy