Обсуждение: Generic hash function?
I'm trying to write a generic equals operator that works on type anyelement, and get it to work with hashing. But to do that I need a generic hash function. Does one exist? And if not, any pointers on how to write one? Brian
Brian Hurt <bhurt@janestcapital.com> writes: > I'm trying to write a generic equals operator that works on type > anyelement, What for? You'd ultimately have to depend on the various actual input types' native equality operators, so I don't see what this'd buy except a layer of useless-seeming overhead. Also, it could only be hashable if the input type's equality is hashable, and we don't have any way to deal with a "sometimes hashable" equality operator. regards, tom lane
Tom Lane wrote:
What I'd like to write is:
CREATE FUNCTION eqnull(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
)
$_$ IMMUTABLE LANGUAGE SQL;
Which changes how nulls are handled. This makes writing certain kinds of queries much easier and much more clear what the query is doing.
Am I going to need to create an operator class for every type?
Brian
Brian Hurt <bhurt@janestcapital.com> writes:I'm trying to write a generic equals operator that works on type anyelement,What for? You'd ultimately have to depend on the various actual input types' native equality operators, so I don't see what this'd buy except a layer of useless-seeming overhead. Also, it could only be hashable if the input type's equality is hashable, and we don't have any way to deal with a "sometimes hashable" equality operator. regards, tom lane
What I'd like to write is:
CREATE FUNCTION eqnull(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
)
$_$ IMMUTABLE LANGUAGE SQL;
Which changes how nulls are handled. This makes writing certain kinds of queries much easier and much more clear what the query is doing.
Am I going to need to create an operator class for every type?
Brian
Brian Hurt <bhurt@janestcapital.com> writes: > Tom Lane wrote: >> What for? > What I'd like to write is [ x=y meaning x IS NOT DISTINCT FROM y ] > Am I going to need to create an operator class for every type? If you want it to be indexable you're going to have to do a lot more work than that. btree generally assumes that the comparison functions are strict, and the other index AMs discriminate against nulls even more strongly, eg, hash doesn't store nulls at all. I think the hash and merge join code is designed to ignore nulls as well. On the whole you'd likely find it a lot simpler to reconsider the ways your application is using NULL. Assuming that NULL = NULL is fundamentally against the SQL spec, and you will be forever swimming upstream if you try to make PG handle it that way. regards, tom lane