Re: Coercing compound types to use generic ROW comparison operators

On Thu, Oct 11, 2007 at 02:52:08PM -0400, Tom Lane wrote:
> Randall Lucas <rlucas@tercent.com> writes:
> > Is there a way I can convince my custom composite data type (point_pk)
> > to use the row-wise comparison functions, so that I don't have to
> > hackishly rewrite the comparison algorithm for each composite type?
>
> regression=# create type point_pk as (x int, y int);
> CREATE TYPE
> regression=# create table foo(f1 point_pk, f2 point_pk);
> CREATE TABLE
> regression=# select * from foo where f1 = f2;
> ERROR:  operator does not exist: point_pk = point_pk
> LINE 1: select * from foo where f1 = f2;
>                                    ^
> HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
>
> regression=# select * from foo where row((f1).*) = row((f2).*);
>  f1 | f2
> ----+----
> (0 rows)
>
> > Using 8.1.5.
>
> ... but I think it only works as of 8.2.

Confirmed; in 8.1.5 the above gives

 ERROR:  column "*" not found in data type point_pk

Since I do have access to the column list for the subtypes (since they
are PK columns for a given table), I just ended up creating operators
for them at the same time as creating the type, building up a string
that creates a comparator function using this general pattern:

 select row(lhs.col1, lhs.col2, lhs.col3) = row(rhs.col1, rhs.col2,
 rhs.col3...)

Still, this would fail in a nested situation because it wouldn't
recurse (if col1 of the compound type were another compound type,
ferinstance), as would your suggestion above.  It might be worthwhile
to allow choosing to use the default ROW comparison operator at
composite type creation (which would provide a more elegant solution to
nested situations).  I acknowledge the unlikeliness that this is a big
problem for most folks, however...

Thanks,

Randall

--
Randall Lucas       Tercent, Inc.       DF93EAD1