[Pljava-dev] ResultSet in and out

Bruce Duncan wrote:

>Let me try to explain what I'm looking to achieve with
>pl/java and hopefully someone can shed a little light
>on how it could be accomplished.
>
>I'd like to write a function which takes in the result
>of a query and returns a ResultSet. I'd like the ddr
>signature not to be bound to a particular table
>rowtype or composite type (is this what the record
>type is for?). So I'd like it to take in the result
>of a query to any table. Then I'd like the function
>to be able to return a ResultSet. This ResultSet
>would be a modification of the one passed in. It
>would add one or more columns and values for each row
>for those columns.
>
>I'm new to Postgres and have looked through the
>pl/java and pl/pgsql docs but didnt see this addressed
>there.
>
>Any suggestions?
>
>
You will run into a couple of limitations immediately but perhaps your
problem can be solved anyway.

The first and biggest limitation is that you can't use SETOF <type> as
argument type. In essence, you cannot pass the result of a query as a
parameter to a function. However, judging from what you want to do (add
columns to each row) that's not the best solution anyway. A function
that will peruse one row at a time is generally a better solution since
it enables the data to be streamed from the source to the client without
ever building up in memory. This would imply a function that has a
record parameter and return a record.

That brings us to the next limitation. PL/Java doesn't handle anonymous
record parameters very well at present. If it doesn't know the exact
type of its input, it will convert it into a String. This is subject to
improvement of course.

Should this be fixed in Java, there's still a third limitation. The
RECORD in itself is not a type per se. It's just a placeholder and when
a function returning RECORD is used, the caller must explicitly state
what that RECORD should look like. In essence, when perusing one row at
a time you would like to write SQL that looks something like this:

SELECT myfunc(x) AS (a record definition) FROM <some source>

that however, is not a legal syntax. The only legal syntax (that I know,
I'm trying to get more info on this) is like this:

SELECT x FROM myfunc(<some source>) AS (a record definition)

which implies that you pass a SETOF as an argument which gets us right
back to the first limitation. Catch 22.

So, what *can* you do?

Well, even if you need one function declaration for each type of record
that you want to use as input, you will still only need one single Java
method that deals with all of them. All known complex types are handled
as single row preset ResultSet instances. So lets say you have the Java
method:

static boolean peruseRow(ResultSet input, ResultSet output)
{
// Do some copying from input to output.
}

you can then declare functions like

CREATE FUNCTION pr1(type1) RETURNS type1m AS 'x.y.z.peruseRow' LANGUAGE
java;

CREATE FUNCTION pr2(type2) RETURNS type2m AS 'x.y.z.peruseRow' LANGUAGE
java;

CREATE FUNCTION pr3(type3) RETURNS type3m AS 'x.y.z.peruseRow' LANGUAGE
java;

(the type<n> and type<n>m are types declared with the CREATE TYPE command)

The
and you would call the functions as:

SELECT pr3(x) FROM type_3 x WHERE ...

I think that is the best you can do at present.

Kind regards,
Thomas Hallgren