Re: Hashable custom types - Mailing list pgsql-hackers

On Fri, Apr 25, 2014 at 4:54 PM, Peter Geoghegan <pg@heroku.com> wrote:
> On Fri, Apr 25, 2014 at 4:47 PM, Paul Ramsey <pramsey@cleverelephant.ca> wrote:
>> Is it possible to make custom types hashable? There's no hook in the
>> CREATE TYPE call for a hash function, but can one be hooked up
>> somewhere else? In an operator?
>
> See 35.14.6., System Dependencies on Operator Classes

Coming back to this, I created an appropriate opclass...

CREATE OR REPLACE FUNCTION geometry_hash_eq(geom1 geometry, geom2 geometry)
RETURNS boolean
AS '$libdir/postgis-2.2', 'lwgeom_hash_eq'
LANGUAGE 'c' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION geometry_hash(geom1 geometry)
RETURNS integer
AS '$libdir/postgis-2.2', 'lwgeom_hash'
LANGUAGE 'c' IMMUTABLE STRICT;

-- Availability: 0.9.0
CREATE OPERATOR == (
LEFTARG = geometry, RIGHTARG = geometry, PROCEDURE = geometry_hash_eq,
COMMUTATOR = '==',
RESTRICT = contsel, JOIN = contjoinsel
);

CREATE OPERATOR CLASS hash_geometry_ops DEFAULT FOR TYPE geometry USING hash AS OPERATOR 1 == (geometry, geometry),
FUNCTION1 geometry_hash(geometry);
 

I even tested that it as an index!
 > create index hashidx on points using hash ( the_geom_webmercator); CREATE INDEX

But when I run my recursive query...

WITH RECURSIVE find_cluster(cartodb_id, cluster_id, geom) AS (
   (SELECT     points.cartodb_id, points.cartodb_id as cluster_id,
points.the_geom_webmercator as geom   FROM points   WHERE points.cartodb_id in (select cartodb_id from points))   UNION
 (SELECT     pts.cartodb_id, n.cluster_id, pts.the_geom_webmercator as geom   FROM points pts   JOIN find_cluster n
ONST_DWithin(n.geom, pts.the_geom_webmercator, 2)   WHERE n.cartodb_id <> pts.cartodb_id)
 
)
SELECT * FROM find_cluster;

It still says I lack the secret sauce...

ERROR:  could not implement recursive UNION
DETAIL:  All column datatypes must be hashable.

What's the sauce?

Thanks!

P




>
>
> --
> Peter Geoghegan