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
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[] ^
