Need efficient way to do comparison with NULL as an option

["D. Dante Lorenso" <dante@xxxxxxxxxxx>]

All,

I'm looking for an operator that will compare NULL with NULL and 
evaluate as TRUE.

I have a BIGINT column which might contain NULL values.  I want to pass 
a value to compare with that column in my WHERE clause.  If the value 
I'm comparing is 0, I want it to match the NULL values.  Here is a 
sample query that I currently use:

  SELECT *
  FROM mytable
  WHERE (col IS NULL AND NULLIF(?, 0) IS NULL) OR col = ?;

The '?' placeholders used in the query will receive the same value which 
might be any one of the following: NULL, 0, 1, 2, 3, etc.

What I'd really like is an operator that will compare NULL with NULL and 
evaluate as TRUE.  Does that exist?

I tried solving this myself, so I have a stored proc like this:

--------------------
CREATE OR REPLACE FUNCTION "public"."is_equal_bigint" (in_val1 bigint, 
in_val2 bigint) RETURNS boolean AS
$body$
BEGIN
    -- both values are null
    IF in_val1 IS NULL AND in_val2 IS NULL THEN
       RETURN TRUE;
    END IF;

    -- values are the same
    IF in_val1 = in_val2 THEN
       RETURN TRUE;
    END IF;

    -- values are different
    RETURN FALSE;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
--------------------

I can use this function as follows:

  SELECT *
  FROM mytable
  WHERE is_equal_bigint(col, NULLIF(?, 0)) IS TRUE;

But I worry that this will not allow my query to use any indexes on 
'col' and will make all my queries slower.

Any recomendations for making my first query above more optimized and 
still efficient?  Does the operator I'm looking for exist?

-- Dante


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend