Search Postgresql Archives

Re: Coercing compound types to use generic ROW comparison operators

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Oct 11, 2007 at 02:52:08PM -0400, Tom Lane wrote:
> Randall Lucas <rlucas@xxxxxxxxxxx> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux