Thread: Polymorphic functions without a type

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

----- 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

nhrcommu@rochester.rr.com wrote:

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

In addition to providing a text function, explicitly assigning a type
also works, so:
select equals(null :: int, null);

also works.

Brian

Brian Hurt <bhurt@janestcapital.com> writes:
> In addition to providing a text function, explicitly assigning a type
> also works, so:
> select equals(null :: int, null);

This must be so if you think about it: the meaning of equality depends
on what type you are talking about, so equals() cannot work in a vacuum.
You have to give the parser some clue what type you want the inputs to
be understood as.

In most real-world cases you're not going to be passing the thing
literal constants, but expressions involving table columns, which will
have well-defined types already.  So I don't see this as a major problem
in practice.  I'd avoid adding a separate equals(text,text) function,
as that's most likely not going to do much except risk "capturing" cases
you'd rather had not gotten interpreted as text equality.

            regards, tom lane