Re: Polymorphic functions without a type

----- Original Message -----
From: Brian Hurt <bhurt@janestcapital.com>
Date: Friday, December 1, 2006 9:35 am
Subject: [NOVICE] Polymorphic functions without a type
To: pgsql-novice@postgresql.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