Thread: Testing for null value in arrays

I am new to the list and somewhat new to PostgreSQL... so, if my
question seems mundane or trivial I apologize. I have a plpgsql function
that performs multiple inserts and updates on a variety of tables.  In
one particular instance the function tests an array for null.  If the
array is not null the record is inserted.  I can get the array to test
true to null but it fails when reversed.

*** A sample and simple version of what I am trying to do ***

CREATE FUNCTION sb_event_insert (_TEXT)
RETURNS bool
AS 'DECLARE
    argv_vector    ALIAS FOR $1;
    evnt_seq    INTEGER;

     BEGIN
    IF argv_vector = \'{}\'
    THEN
        evnt_seq := nextval(\'event_sequence\');
        INSERT INTO argv VALUES (evnt_seq, argv_vector);
    END IF;
END;'
LANGUAGE 'plpgsql';


This evaluates successfully and without errors... but, obviously I am
testing for not null.  It seems inefficient to use an IF - THEN - ELSE
statement here.  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.

Regards, Michael Dunn

Michael Dunn <michael@2cactus.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