question about alternate ordering of results

[hamann.w@xxxxxxxxxxx]

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





-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general