Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group