postgresql - 如何在 PostgreSQL 中为具有 int4range 值的 hstore 类型创建一个运算符

Question

我有一个包含 HSTORE 列“ext”的表，其中的值是一个 int4 范围。一个例子:

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

但是，当我尝试为此创建表达式索引时，出现错误:

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.

如何为此创建运算符？

注意

每条记录都可能有自己唯一的键集。每个键代表一个属性，值代表值范围。所以不是所有的记录都会有“p1”。将其视为 hstore 中的 EAV 模型。

Answer 1

我没有收到该错误 - 我收到“索引表达式中的函数必须标记为 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)

我猜转换不是不可变的，因为您可以将范围的默认格式从包含...排除“[...)”更改为其他格式。不过你大概不会那样做。

显然，您会希望真正的函数能够处理诸如缺少“p1”条目、格式不正确的范围值等问题。