Re: Polymorphic functions without a type

----- Original Message -----
From: Brian Hurt <bhurt(at)janestcapital(dot)com>
Date: Friday, December 1, 2006 9:35 am
Subject: [NOVICE] Polymorphic functions without a type
To: pgsql-novice(at)postgresql(dot)org

> 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

May be some help coming (search for NULL within the page):
http://www.postgresql.org/docs/8.2/static/release-8-2.html