[Pljava-dev] testing for equality in user-defined types in select statements

I have a UDT for rational numbers. I want to be able to add it in select
statements but it doesn't work.

As a really simple example consider two tables

create table foo (foo varchar(20), value rational);
create table bar (bar varchar(20), value rational);

select f.value, f.foo, b.bar from foo f join bar b on (f.value =
b.value);

I vaguely recall that there were standard function names for this when I
played with C UDT years ago but that was PSQL version 5.x or 6.x. Is that
still the case? In either case the wiki should be updated.

There should be additional functions for ordering.

Thanks,

Bear
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20111226/82e546d2/attachment.html>

On 12/26/2011 7:41 PM, Bear Giles wrote:
> I have a UDT for rational numbers. I want to be able to add it in
> select statements but it doesn't work.
> >
> select f.value, f.foo, b.bar from foo f join bar b on (f.value =
> b.value);
>
> I vaguely recall that there were standard function names for this when I
> played with C UDT years ago but that was PSQL version 5.x or 6.x. Is
> that still the case? In either case the wiki should be updated.
>

You are looking for CREATE OPERATOR for equality/comparison and CREATE
OPERATOR CLASS for ordering definition. These are no different for
pljava types vs any other implemented type.

Kris Jurka

Thanks.

Three more questions....

1) what are the two methods that are required for hash joins. I'm sure
they're related to the hashCode method but I haven't come across how to
make the link. I might have overlooked it though - I've been reading a lot
of documentation but Google often returns really old documentation and I
don't always catch that. :-(

2) I'm having problems defining one of my operations. The UDT is
"Rational", for rational numbers. I don't have a problem defining the
ordering operations between two rational values but I'm also trying to
define ordering operations between a Rational and a float8.

I've defined my method

java:
public static boolean lessThan(Rational p, double q);

postgresql.ddr:
CREATE FUNCTION javatest.rational_lt(javatest.rational, float8)
RETURNS bool
AS 'sandbox.Rational.lessThan'
LANGUAGE JAVA IMMUTABLE STRICT;

CREATE OPERATOR < (
leftarg = javatest.rational, rightarg = float8, procedure =
javatest.rational_lt,
commutator = > , negator = >=
);

but when I try to load the jar file I get

ERROR: function javatest.rational_lt(javatest.rational, double
precision) does not exist
CONTEXT: SQL statement "CREATE OPERATOR < ( leftarg =
javatest.rational, rightarg = float8, procedure = javatest.rational_lt,
commutator = > , negator = >= )"

Ideas?

3) What about aggregate functions? Sum(), min(), etc. Are they just
functions that take a SETOF (sql) and array (java)?

I know that casts are created with 'CREATE CAST' but haven't tried adding a
cast from double to rational yet.

BTW I'm putting up an introduction on my blog,
http://invariantproperties.com/. I'm currently working on part 4, types,
and trying to hit everything that I would want from the java side to make
it look like a first-class object instead of just a way to store data -
integration with hibernate, the operations and indexes, etc.

On Mon, Dec 26, 2011 at 9:02 PM, Kris Jurka <jurka at ejurka.com> wrote:

> On 12/26/2011 7:41 PM, Bear Giles wrote:
>
>> I have a UDT for rational numbers. I want to be able to add it in
>> select statements but it doesn't work.
>> >
>> select f.value, f.foo, b.bar from foo f join bar b on (f.value =
>> b.value);
>>
>> I vaguely recall that there were standard function names for this when I
>> played with C UDT years ago but that was PSQL version 5.x or 6.x. Is
>> that still the case? In either case the wiki should be updated.
>>
>>
> You are looking for CREATE OPERATOR for equality/comparison and CREATE
> OPERATOR CLASS for ordering definition. These are no different for pljava
> types vs any other implemented type.
>
> Kris Jurka
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20111227/6bf576f6/attachment.html>

On 12/27/2011 9:41 AM, Bear Giles wrote:
>
> 1) what are the two methods that are required for hash joins. I'm sure
> they're related to the hashCode method but I haven't come across how to
> make the link.

You're still thinking about the Java side, but you really need to think
about this from the postgresql side. Postgresql determines the hash
function for a type by looking up it's hash operator class definition.
It might be best to look at some sample opclass definitions for btree
and hash to figure out how to define the ordering and hashing operations
you want. Perhaps look at contrib/isn?

> 2) I'm having problems defining one of my operations. The UDT is
> "Rational", for rational numbers. I don't have a problem defining the
> ordering operations between two rational values but I'm also trying to
> define ordering operations between a Rational and a float8.
>

I'm not sure what's going on here.

> 3) What about aggregate functions? Sum(), min(), etc. Are they just
> functions that take a SETOF (sql) and array (java)?
>

See CREATE AGGREGATE.

Kris Jurka