Polymorphic functions without a type

From: Brian Hurt <bhurt(at)janestcapital(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Polymorphic functions without a type
Date: 2006-12-01 14:33:47
Message-ID: 45703D4B.4060609@janestcapital.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message nhrcommu 2006-12-01 15:24:09 Re: Polymorphic functions without a type
Previous Message Carlson, James (Jim) 2006-12-01 12:31:59 Messed up Postgresql