Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> Tom> Lastly, your proposed use-case has some attraction, but this
> Tom> proposal only supports it if the column you need to be differently
> Tom> sorted is textual. What if the sort columns are all numerics and
> Tom> timestamps?
> There are already trivial ways to reverse the orders of those, viz.
> (-number) and (-extract(epoch from timestampcol)). The lack of any
> equivalent method for text is what prompted this idea.
Those "trivial ways" have failure cases, eg with INT_MIN. I don't buy
that this argument justifies introducing a kluge into text comparison.
> Tom> Thinking about that, it seems like what we'd want is some sort of
> Tom> more-general notion of row comparison, to express "bounded below
> Tom> in an arbitrary ORDER BY ordering". Not quite sure what it ought
> Tom> to look like.
> Well, one obvious completely general method is to teach the planner
> (somehow) to spot conditions of the form
> (a > $1 OR (a = $1 AND b > $2) OR (a = $1 AND b = $2 AND c > $3) ...)
> etc. and make them indexable if the sense of the > or < operator at
> each step matched an ASC or DESC column in the index.
I think really the only attraction of that is that it could be argued
to be standard --- but I rather doubt that it's common for DBMSes to
recognize such things. It'd certainly be a royal pain in the rear
both to implement and to use, at least for more than about two sort
columns.
Back at
https://www.postgresql.org/message-id/10492.1531515255%40sss.pgh.pa.us
I proposed that we might consider allowing row comparisons to specify
an explicit list of operators:
>> One idea for resolving that is to extend the OPERATOR syntax to allow
>> multiple operator names for row comparisons, along the lines of
>> ROW(a,b) OPERATOR(pg_catalog.<, public.<) ROW(c,d)
I wonder whether it'd be feasible to solve this problem by doing that
and then allowing the operators to be of different comparison types,
that is "ROW(a,b) OPERATOR(<, >) ROW(c,d)". The semantics would be
that the first not-equal column pair determines the result according
to the relevant operator. But I'm not quite sure what to do if the
rows are in fact equal --- if some of the operators are like "<" and
some are like "<=", what should the result be? Maybe let the last
column's operator decide that?
regards, tom lane
