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