I noticed something odd when trying to use the row-wise comparison
mentioned in the release notes for 8.2 and in the docs
http://developer.postgresql.org/pgdocs/postgres/functions-comparisons.html#ROW-WISE-COMPARISON
This sets up a suitable test:
create type myrowtype AS (a integer, b integer);
create table myrowtypetable (rowval myrowtype);
insert into myrowtypetable select (a, b)::myrowtype from generate_series(1,5) a, generate_series(1,5) b;
First I get this error:
select rowval < rowval from myrowtypetable ;
ERROR: operator does not exist: myrowtype < myrowtype
LINE 1: select rowval < rowval from myrowtypetable ; ^
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
OK, I guess I can live with that. I did create a new type, and there are
no operators for it...
Now, I can do the following (pointless) query
select ROW((rowval).*) < ROW((rowval).*) from myrowtypetable ;
and I get 25 rows of 'f'. So far so good.
But if I try to do
select rowval from myrowtypetable ORDER BY ROW((rowval).*);
ERROR: could not identify an ordering operator for type record
HINT: Use an explicit ordering operator or modify the query.
or even
select rowval from myrowtypetable ORDER BY ROW((rowval).*) USING <;
ERROR: operator does not exist: record < record
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
I know that that less-than operator exists, because I just used it in the
query that worked above. It seems that ORDER BY just can't find it for
some reason.
Is it supposed to not work in order by? That doesn't really make sense to
me why order by should be special for this.
--
All extremists should be taken out and shot.
