OK, so I've gotten annoyed at how nulls get compared- sometimes the
default behavior is what I want, but sometimes it isn't. And I know
that the behavior of nulls in Postgres is what the standard requires, so
that shouldn't change. But I'm looking at what it'd take to supply a
new set of operators in Postgres to provide "alternate" null compares.
The first problem I've hit in looking at this is using polymorphic
functions. I've defined a function:
CREATE FUNCTION equals(anyelement, anyelement) RETURNS BOOLEAN AS $_$
SELECT
(CASE
WHEN $1 IS NULL AND $2 IS NULL THEN TRUE
WHEN ($1 IS NULL AND $2 IS NOT NULL)
OR ($1 IS NOT NULL AND $2 IS NULL)
THEN FALSE
ELSE $1 = $2
END
)
$_$ LANGUAGE SQL;
This function works mostly like I wanted it to:
> bhurt2_dev=# SELECT equals(1,2);
> equals
> --------
> f
> (1 row)
>
> bhurt2_dev=# SELECT equals(1,1);
> equals
> --------
> t
> (1 row)
>
> bhurt2_dev=# SELECT equals(1,null);
> equals
> --------
> f
> (1 row)
>
> bhurt2_dev=# SELECT equals(null,1);
> equals
> --------
> f
> (1 row)
>
The problem here is:
> bhurt2_dev=# SELECT equals(null,null);
> ERROR: could not determine anyarray/anyelement type because input has
> type "unknown"
> bhurt2_dev=#
So the question is: how do I fix this? Or do I have to produce a
different equals() function for every type?
Brian