CODEWITHSUNDEEP
postgresqloperatorspostgresql-9.2database-indexeshstore
IamIC
IamIC

Reputation: 18239

How to create an operator in PostgreSQL for the hstore type with an int4range value

I have a table with an HSTORE column 'ext', where the value is an int4range. An example:

"p1"=>"[10, 18]", "p2"=>"[24, 32]", "p3"=>"[29, 32]", "p4"=>"[18, 19]"

However, when I try to create an expression index on this, I get an error:

CREATE INDEX ix_test3_p1
ON test3
USING gist
(((ext -> 'p1'::text)::int4range));

ERROR: data type text has no default operator class for access method "gist" SQL state: 42704 Hint: You must specify an operator class for the index or define a default operator class for the data type.

How do I create the operator for this?

NOTE

Each record may have its own unique set of keys. Each key represents an attribute, and the values the value range. So not all records will have "p1". Consider this an EAV model in hstore.

Upvotes: 2

Views: 648

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22893

I don't get that error - I get "functions in index expression must be marked IMMUTABLE"

CREATE TABLE ht (ext hstore);
INSERT INTO ht VALUES ('p1=>"[10,18]"'), ('p1=>"[99,99]"');
CREATE INDEX ht_test_idx ON ht USING GIST ( ((ext->'p1'::text)::int4range) );
ERROR:  functions in index expression must be marked IMMUTABLE

CREATE FUNCTION foo(hstore) RETURNS int4range LANGUAGE SQL AS $$ SELECT ($1->'p1')::int4range; $$ IMMUTABLE;
CREATE INDEX ht_test_idx ON ht USING GIST ( foo(ext) );
SET enable_seq_scan=false;
EXPLAIN SELECT * FROM ht WHERE foo(ext) = '[10,19)';
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Index Scan using ht_test_idx on ht  (cost=0.25..8.52 rows=1 width=32)
   Index Cond: (foo(ext) = '[10,19)'::int4range)

I'm guessing the cast isn't immutable because you can change the default format of the range from inclusive...exclusive "[...)" to something else. You presumably won't be doing that though.

Obviously you'll want your real function to deal with things like missing "p1" entries, badly formed range values etc.

Upvotes: 1

Related Questions