[postgis-users] postgis and pl/java

[Markus Schaber]

Hi, David,

David Potts wrote:
> Has anybody tried to mix PL/Java and postgis ?
> 
> It seems that PL/Java comes with a special version of the java postgres
> driver, postgis has an extended version of normal postgres java driver.

No, it doesn't. It has its own implementation of the JDBC interface, and
nearly nothing in common besides implementing the SUN JDBC standard.

The main reason for this is that the standalone JDBC driver uses TCP
connections to the server, whereas pl/java uses the server internal SPI
interface via Java Native Interface and C glue code.

> Just wondering if anybody has tried mxing these two products before?

Yes, I have.

The prototypes for a type Mapping for PostGIS Geometries are currently
in postgis SVN, in the java/pljava directory.

But I doubt that they will work with the released 1.3 version of pljava,
as this version is buggy wr/t variable length datatypes. Actually, my
code for PostGIS and the example "varlentuple" data type I developed for
debugging purposes was the first attempt to actually implement a java
mapping for a variable length custom datatype. All the examples and
regression code only implements fixed length datatype mappings, apart
from the supported PostgreSQL builtin types like varchar, which are
special-cased in the driver.

I'm currently working on a patchset with the author of pljava (see
discussions on the pljava mailing list), but things are going rather
slow due to time constraints on both sides. I hope to finish the
patchwork this weekend, so it might be included in the next release of
pljava.

As soon as that's finished, the current pljava code in postgis should
work, but it's still missing a build system, documentation, and some
code merging between pljava and the "normal" jdbc implementation.

> I have a table with some values in it, when they get updated I want to
> fire a trigger which will result in an update spatial setting in an table.

As long as there's no type mapping, the PostGIS types will be mapped to
String in pljava, that means that you can get PostGIS geometry and boxes
in their text representation (the same you see using psql command line),
and you can parse them "manually" using the PostGIS java code. For
return values / out parameters, do the opposite, return the String
representation of the data.

Alternatively, write your trigger using plpgsql, which supports all
datatypes.

HTH,
Markus