Re: xpath() subquery for empty array

Scott Bailey wrote:
> 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(at)samason(dot)me(dot)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.
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.:

SELECT x
FROM (SELECT xpath('//entry[contains(p, ''searchtext'')]/@*', docxml)
AS x FROM docs) AS y WHERE x <> [test for empty array?????????]

-- Roy