CODEWITHSUNDEEP
postgresqlpostgresql-9.2database-indexescustom-type
IamIC
IamIC

Reputation: 18239

Create operator / function for custom type in PostgreSQL 9.2

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?

Upvotes: 0

Views: 416

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

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

  1. Consult the documentation regarding GIN opclasses.

  2. Write a set of IMMUTABLE functions to handle those.

  3. Write a set of operators based on those functions.

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

Upvotes: 2

Related Questions