Thread: need help : how to replace / extend existing SQL operators ?

hi,

i've develloped a contrib library for postgresql which use GPC to
enhance polygons support.(working intersections ;)

but i've got a big problem:

i've done this :

=========================================================================

drop function "poly_intersect" (polygon, polygon);
drop operator # (polygon, polygon);
create function "poly_intersect"(polygon, polygon) returns polygon
  as '$libdir/polygons_utils'
  language 'C';

create operator # (
        leftarg = polygon,
        rightarg = polygon,
        procedure = poly_intersect,
        commutator = #
);

-- test if a polygon intersect (/ overlap) another.
--
drop function "poly_is_intersecting" (polygon, polygon);
drop operator ?# (polygon, polygon);
create function "poly_is_intersecting"(polygon, polygon) returns boolean
  as '$libdir/polygons_utils'
  language 'C';

create operator ?# (
        leftarg = polygon,
        rightarg = polygon,
        procedure = poly_is_intersecting,
        commutator = ?#
);

-- test if a polygon overlap to left another.
--
drop function "poly_overleft" (polygon, polygon);
drop operator &< (polygon, polygon);
create function "poly_overleft"(polygon, polygon) returns boolean
  as '$libdir/polygons_utils'
  language 'C';

create operator &< (
        leftarg = polygon,
        rightarg = polygon,
        procedure = poly_overleft,
        commutator = &>
);

-- test if a polygon overlap to right another
--
drop function "poly_overright" (polygon, polygon);
drop operator &> (polygon, polygon);
create function "poly_overright"(polygon, polygon) returns boolean
  as '$libdir/polygons_utils'
  language 'C';

create operator &> (
        leftarg = polygon,
        rightarg = polygon,
        procedure = poly_overright,
        commutator = &<
);

-- Test if a polygon overlap another
--
drop function "poly_overlap" (polygon, polygon);
drop operator && (polygon, polygon);
create function "poly_overlap"(polygon, polygon) returns boolean
  as '$libdir/polygons_utils'
  language 'C';

create operator && (
        leftarg = polygon,
        rightarg = polygon,
        procedure = poly_overlap,
        commutator = &&
);

-- Test if a polygon contains another one.
--
drop function "poly_contain" (polygon, polygon);
drop operator @ (polygon, polygon);
create function "poly_contain"(polygon, polygon) returns boolean
  as '$libdir/polygons_utils'
  language 'C';

create operator @ (
        leftarg = polygon,
        rightarg = polygon,
        procedure = poly_contain,
        commutator = @
);

-- Return the gravity center of a polygon
--
drop function "poly_center" (polygon);
drop operator @@ (polygon);
create function "poly_center"(polygon) returns point
  as '$libdir/polygons_utils'
  language 'C';

create operator @@ (
        leftarg = polygon,
        rightarg = NONE,
        procedure = poly_center
);

=========================================================================

as you can see, i've rewrited some existing polygons fonctions that i
found not working correctly. I wanted to update functions and operators
lists to use thoses new functions.

but, after executed these request (yeeaahh.. i know .. the polygon
center one is bogus but i don't care.. for the moment.. ) , i've go this
error message trying to use

?#(polygon, polygon) :

=========================================================================
ERROR: FillScanKeyEntry: unknown operator 486

Your query:

SELECT COUNT(sensors_datas_list.record_index) AS records_count,
min(sensors_datas_list.zone_start_date) AS file_start_date,
max(sensors_datas_list.zone_end_date) AS file_end_date,
group_poly(sensors_datas_list.datas_zone) AS file_zone,
sensors_datas_list.file_id, files_list.file_name FROM files_list,
sensors_datas_list, sensors_list, satellites_list WHERE
sensors_datas_list.file_id = files_list.file_id AND
sensors_list.sensor_id = sensors_datas_list.sensor_id AND
sensors_list.satellite_id = satellites_list.satellite_id AND
sensors_list.sensor_id = 6 AND sensors_datas_list.datas_zone ?#
polygon(box '((166.0,0.0),(170.0,6.0))') GROUP BY files_list.file_name,
sensors_datas_list.file_id

=========================================================================

it seems that it try to use the old intersection test operator.. any
help should be greatly apprecied ;)

Thanx

Pierre-Yves Landuré

Pierre-Yves LANDURE <pylandur@ifremer.fr> writes:
> ERROR: FillScanKeyEntry: unknown operator 486

You broke the index opclass for rtree on polygon.  See manual
section about interfacing extensions to indexes.

            regards, tom lane