Re: Testing for null value in arrays

Michael Dunn <michael(at)2cactus(dot)com> writes:
> IF argv_vector = \'{}\'
> THEN
> This evaluates successfully and without errors... but, obviously I am
> testing for not null.

Um ... no ... you are testing for not-empty-array. A zero-element array
is not the same thing as an SQL NULL. Not sure if the distinction is
important for your purposes or not, but there *is* a difference.

> So, by reversing the evaluation

> IF argv_vector != \'{}\'
> the function when executed fails with the following error:
> SELECT sb_event_insert ('{}');
> ERROR: Unable to identify an operator '<>' for types '_text' and
> 'unknown'. You will have to retype this query using an explicit cast.

A moment's poking into pg_operator shows that there is an '=' operator
for text[], but not a '<>' operator:

regression=# select * from pg_operator where oprname = '=' and
regression-# oprleft = (select oid from pg_type where typname = '_text');
oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin
---------+----------+---------+---------+-----------+------------+---------+----------+-----------+--------+-----------+------------+------------+----------+---------+-----------
= | 256 | 0 | b | t | f | 1009 | 1009 | 16 | 381 | 0 | 0 | 0 | array_eq | eqsel | eqjoinsel
(1 row)

regression=# select * from pg_operator where oprname = '<>' and
regression-# oprleft = (select oid from pg_type where typname = '_text');
oprname | oprowner | oprprec | oprkind | oprisleft | oprcanhash | oprleft | oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop | oprcode | oprrest | oprjoin
---------+----------+---------+---------+-----------+------------+---------+----------+-----------+--------+-----------+------------+------------+---------+---------+---------
(0 rows)

This is clearly an omission that should be rectified (feel free to
submit a patch!), but it's not exactly catastrophic. Write
"NOT (argv_vector = \'{}\')" if you feel the need to cast your IFs
in that direction.

regards, tom lane