Thread: Coercing compound types to use generic ROW comparison operators

I am storing a rowtype that keeps the primary key column(s) of another
table.  E.g.,

 create table point (x int, y int, stuff text, primary key
(x, y));

then, think:

 create type point_pk as (x int, y int).

When I go to compare point_pks against one another I get errors about
missing comparison operators.

HOWEVER, I can do this no problem:

 select row(1,2)=row(2,3);

I would REALLY like to be able to use the generic row comparison
functions, which, as detailed in the manual, are equivalent to
comparing elements left-to-right.

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?

Using 8.1.5.

Thanks,

Randall

--
Randall Lucas       Tercent, Inc.       DF93EAD1

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?

Well, you can do this ...

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.

            regards, tom lane

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

Randall Lucas <rlucas@tercent.com> writes:
> 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).

You are incorrectly supposing that there *is* such an animal as a
default row comparison operator --- actually, ROW() = ROW() is expanded
at parse time into field-by-field comparisons.  This is usually a good
thing since it gives the planner more flexibility.

            regards, tom lane

On 10/11/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Randall Lucas <rlucas@tercent.com> writes:
> > 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).
>
> You are incorrectly supposing that there *is* such an animal as a
> default row comparison operator --- actually, ROW() = ROW() is expanded
> at parse time into field-by-field comparisons.  This is usually a good
> thing since it gives the planner more flexibility.

AIUI, the biggest problem with the current behavior is that there is
no way to usefully index composite types, it looks like

create index bar_idx on bar(f);
create index bar_idx on bar((f).*);
create index bar_idx on bar((f).a, (f).b);

are all invalid. the only way to do it that i can see is to create a
separate function for each field of the composite you want to index.

merlin

"Merlin Moncure" <mmoncure@gmail.com> writes:
> AIUI, the biggest problem with the current behavior is that there is
> no way to usefully index composite types, it looks like

> create index bar_idx on bar(f);
> create index bar_idx on bar((f).*);
> create index bar_idx on bar((f).a, (f).b);

The last case works, you just don't have enough parentheses.

regression=# create type mytype as (a int, b float);
CREATE TYPE
regression=# create table foo(f mytype);
CREATE TABLE
regression=# create index fooi on foo(((f).a), ((f).b));
CREATE INDEX

            regards, tom lane

On 10/12/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
> > AIUI, the biggest problem with the current behavior is that there is
> > no way to usefully index composite types, it looks like
>
> > create index bar_idx on bar(f);
> > create index bar_idx on bar((f).*);
> > create index bar_idx on bar((f).a, (f).b);
>
> The last case works, you just don't have enough parentheses.
>
> regression=# create type mytype as (a int, b float);
> CREATE TYPE
> regression=# create table foo(f mytype);
> CREATE TABLE
> regression=# create index fooi on foo(((f).a), ((f).b));
> CREATE INDEX

wow, thats pretty neat! (although:
create index fooi on foo(((f).a));
feels awfully weird).

for the record, creating indexes this way works fully with row
comparison strategies (in 8.2+):
select * from foo where ((f).a, (f).b) > (5, 0.6) order by (f).a, (f).b limit 1;
will use the 'fooi' index above.

merlin