Re: merge-join for domain with underlying type text - Mailing list pgsql-sql

OK, I ended up wrapping up the text equality and comparison functions 
in simple sql functions that explicitly take my domain as the type, 
then added =, <>, <, <=, >, >= operators pointing to those functions, 
and now I can perform full outer joins with my domain "literal."

See below for ugly code.

Hackers, why is this?  It seems to me that since everything else (or 
everything I've run into so far, up to the full outer joins) is 
implicitly the same for a simple domain and its underlying base type, 
that it would make sense if this, too, Just Worked.  Perhaps we could 
have the create domain function implicitly perform the operator mapping 
to underlying basetypes to permit merge joins?

Best,

Randall

-- begin ugly code:

create domain literal as text;

create or replace function literaleq(literal, literal) returns boolean 
as ' select texteq($1::text, $2::text);
' language sql;

create or replace function literalne(literal, literal) returns boolean 
as ' select textne($1::text, $2::text);
' language sql;

create or replace function literal_lt(literal, literal) returns boolean 
as ' select text_lt($1::text, $2::text);
' language sql;

create or replace function literal_le(literal, literal) returns boolean 
as ' select text_le($1::text, $2::text);
' language sql;

create or replace function literal_gt(literal, literal) returns boolean 
as ' select text_gt($1::text, $2::text);
' language sql;

create or replace function literal_ge(literal, literal) returns boolean 
as ' select text_ge($1::text, $2::text);
' language sql;

create operator < ( leftarg = literal, rightarg = literal, procedure = literal_lt, commutator = >, negator = >=,
restrict= scalarltsel, join = scalarltjoinsel
 
);
create operator <= ( leftarg = literal, rightarg = literal, procedure = literal_le, commutator = >=, negator = >,
restrict= scalarltsel, join = scalarltjoinsel
 
);
create operator > ( leftarg = literal, rightarg = literal, procedure = literal_gt, commutator = <, negator = <=,
restrict= scalargtsel, join = scalargtjoinsel
 
);
create operator >= ( leftarg = literal, rightarg = literal, procedure = literal_ge, commutator = <=, negator = <,
restrict= scalargtsel, join = scalargtjoinsel
 
);


create operator = ( leftarg = literal, rightarg = literal, procedure = literaleq, commutator = =, negator = <>,
restrict= eqsel, join = eqjoinsel, hashes, sort1 = <, sort2 = <
 
);

create operator <> ( leftarg = literal, rightarg = literal, procedure = literaleq, commutator = <>, negator = =,
restrict= neqsel, join = neqjoinsel, hashes, sort1 = <, sort2 = <
 
);





On Saturday, May 10, 2003, at 07:40 PM, Randall Lucas wrote:

> create table textual_test_1 (
>  key textual_test,
>  stuff varchar
> );
> insert into textual_test_1 values ('alpha', 'asdflkjasdf');
> insert into textual_test_1 values ('bravo', 'asdfdasfsaff');
> insert into textual_test_1 values ('delta', 'asdfasfdas');
> create table textual_test_2 (
>  other_key textual_test,
>  more_stuff varchar
> );
> insert into textual_test_2 values ('charlie', 'asdfasfasfda');
> insert into textual_test_2 values ('delta', 'asgasgdda');
> insert into textual_test_2 values ('echo', 'asdasfsdfsfda');
> select * from textual_test_1 full outer join textual_test_2 on 
> textual_test_1.key = textual_test_2.other_key;