Hi,
in the old days (version 7.x) I created a set of btree operators
create operator <& (
procedure = mytext_lt,
leftarg = text,
rightarg = text,
commutator = >&,
negator = >=&,
restrict = scalarltsel,
join = scalarltjoinsel
);
etc. for a completeoperator class
create operator class mytext_ops
for type text using btree as
operator 1 <& (text,text),
....
);
Some tables have an index using this operator class
create index blah_t on blah using btree (col1 mytext_ops);
I also created a few extra operators
create operator <&- (
procedure = mytext_lt_x,
leftarg = text,
rightarg = text,
commutator = >&-,
negator = >=&-,
restrict = scalarltsel,
join = scalarltjoinsel
);
and could use them in a query, like
select .... from blah order by col1;
select .... from blah order by col1 using <&;
select .... from blah order by col1 using <&-;
(resulting in 3 different result orderings)
BTW: the difference between the two ops is "natural" ordering of numerics: if two
strings start to differ at "3rd" vs "17th", the <& returns them in alpha sort and the <&-
produces proper numeric order
Now, in versions 8 and later the "using <&-" is rejected,
the ordering op "needs to be < or > member of a btree operator class".
What is needed to create the old behaviour again
- create a complete operator class, including new names for the unchanged equals/not equals function?
- adding another index to get the feature
Is this relevant to performance? I guess that an index using one opclass and ordering using the
other one would result in an index scan to locate the data and then an extra ordering pass to
order them the other way
Regards
Wolfgang Hamann
