On 10/20/06, Jeremy Drake <[EMAIL PROTECTED]> 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-COMPARISONThis 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
