Create operator / function for custom type in PostgreSQL 9.2

Question

I have the following custom type:

CREATE TYPE param_range AS (  
    param smallint,
    range int4range
);

The following table:

CREATE TABLE test4
(
    id serial NOT NULL,
    geo point,
    ext param_range[]
)

The following indexes:

CREATE INDEX ix_test4_geo ON test4 USING GIST ((geo));
CREATE INDEX ix_test4_ext on test4 USING GIN (ext);

The GIN index requires an operator / function for the custom type. How do I do this?

Chris Travers · Accepted Answer · 2013-11-02 12:39:42Z

2

The GIN index doesn't just require a custom operator. It requires a whole family of operators. Basically you need to:

Consult the documentation regarding GIN opclasses.
Write a set of IMMUTABLE functions to handle those.
Write a set of operators based on those functions.
Tie those together in a custom operator class.

This isn't a simple, small amount of work. It requires a fair bit of though (what does "overlap" mean in the context of your type?" and so you need to expect to spend a fair bit of time in the design phase.

Basically if you want GIST/GIN support you are designing a custom type not just for storage but for operational purposes. This is a project.

answered Nov 2, 2013 at 12:39

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IamIC Over a year ago

Thanks Chris. This dawned on me after digging deep into online documentation, and when no one answered.

losthorse Over a year ago

would you please expand a bit on point number 4? I have asked the question here: stackoverflow.com/q/34407438/1319179

Collectives™ on Stack Overflow

Create operator / function for custom type in PostgreSQL 9.2

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related