agora inbox for pljava-dev@postgresql.org
help / color / mirror / Atom feed[Pljava-dev] ResultSet in and out
2+ messages / 0 participants
[nested] [flat]
* [Pljava-dev] ResultSet in and out
@ 2005-04-20 23:21
2005-04-21 08:35 ` [Pljava-dev] ResultSet in and out
0 siblings, 1 reply; 2+ messages in thread
From: @ 2005-04-20 23:21 UTC (permalink / raw)
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?
Thanks in advance.
Bruce
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* [Pljava-dev] ResultSet in and out
2005-04-20 23:21 [Pljava-dev] ResultSet in and out
@ 2005-04-21 08:35 `
0 siblings, 0 replies; 2+ messages in thread
From: @ 2005-04-21 08:35 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-04-21 08:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-20 23:21 [Pljava-dev] ResultSet in and out
2005-04-21 08:35 `
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox