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);
regression=# create table foo(f1 point_pk, f2 point_pk);
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,

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...



Randall Lucas Tercent, Inc. DF93EAD1

Search Discussions

Discussion Posts


Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 7 | next ›
Discussion Overview
grouppgsql-general @
postedOct 11, '07 at 2:08a
activeOct 12, '07 at 4:00p



site design / logo © 2022 Grokbase