Re: row-wise comparison question/issue

On 10/20/06, Jeremy Drake <pgsql(at)jdrake(dot)com> wrote:
> 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.

that would be neat. i know that row construction and comparison as
currently implemented is sql standard...is the stuff you are
suggesting also standard? (im guessing no).

I'll throw something else on the pile:

esilo=# select (foo).* from foo order by (foo).*;
ERROR: column foo.* does not exist

esilo=# select (foo).* from foo;
a | b | c
---+---+---
(0 rows)

seems a little contradictory...

note jeremy that the more common use of row comparison would be to
construct rows on the fly, usually on fields comprising a key with an
explicit order by:

select a,b,c from foo where (a,b,c) > (1,2,3) order by a,b,c;

works fine

merlin