[Pljava-dev] Calling pl/pgsql functions from pl/java?

That's probably a question for the PostgreSQL guys, but it looks like
the function isn't actually being called until the moment that the
value is needed.

J

On Dec 10, 2007, at 9:31 PM, Lucas Madar wrote:

> Oops. That was a stupid mistake in my dummy code. In the much more
> complicated code, I've run into an interesting observation.
>
> If I call a function with select function(arg1, arg2) and then
> ignore any returned result set, the function doesn't even get
> executed.
> If the function doesn't have a return (a null), there's nothing I
> can do.
>
> If the function, however, returns something and I process its
> resultset, the function is executed.
>
> ie..
>
> Statement b =
> dbq.getConnection().createStatement();
> ResultSet res = b.executeQuery("SELECT
> qappVMeasurementResultReadingOpSum('asdf', 'asdf')");
> res.close();
> Does not call the function.
>
> Statement b =
> dbq.getConnection().createStatement();
> res = b.executeQuery("SELECT
> qappVMeasurementResultReadingOpSum('asdf', 'asdf')");
> res.next();
> System.out.println(res.getString(1));
> res.close();
>
> *DOES* call the function.
>
> What's up with that?
>
> - Lucas
>
> Jeffrey Lyon wrote:
>> Lucas,
>>
>> You need to position the ResultSet:
>>> ResultSet res = stmt.getResultSet();
>> res.next();
>>>
>>> ret = res.getString(1);
>>> res.close();
>>> stmt.close();
>>> sql.close();
>>
>> J
>>
>>
>> On Dec 10, 2007, at 8:29 PM, Lucas Madar wrote:
>>
>>> Hello,
>>>
>>> I'm trying to call preexisting plpgsql functions from pljava. I've
>>> been
>>> beating my head against a wall with them all day, and I can't seem
>>> to
>>> figure out what I'm doing wrong. Apologies if this is too much
>>> junk, but
>>> this is a low-traffic list ;)
>>>
>>> Thanks,
>>> Lucas Madar
>>>
>>>
>>>
>>> Given the following setup, why does this happen? I can't seem to
>>> call
>>> *any* plpgsql functions from within pl/java.
>>>
>>> create or replace function pljava_test_internal(integer) returns
>>> varchar
>>> as 'stupid.test.Dummy.test_internal' LANGUAGE 'javaU' VOLATILE;
>>> create or replace function pljava_test_external(integer) returns
>>> varchar
>>> as 'stupid.test.Dummy.test_external' LANGUAGE 'javaU' VOLATILE;
>>>
>>> CREATE OR REPLACE FUNCTION plpgsql_test_external(varchar) RETURNS
>>> varchar AS $$
>>> BEGIN
>>> return $1 || ' from plpgsql!';
>>> END;
>>> $$ LANGUAGE plpgsql VOLATILE;
>>>
>>> package stupid.test;
>>> import java.sql.*;
>>> public class Dummy {
>>>
>>> public static String test_internal(int number) {
>>> return "internal test la la la " + number;
>>> }
>>>
>>> public static String test_external(int number) throws
>>> SQLException {
>>> String text = "internal test la la la " + number;
>>> Connection sql =
>>> DriverManager.getConnection("jdbc:default:connection");
>>> String ret;
>>>
>>> Statement stmt = sql.createStatement();
>>> if(stmt.execute("SELECT plpgsql_test_external('" +
>>> text
>>> + "');"))
>>> throw new SQLException("No results from
>>> query?");
>>> ResultSet res = stmt.getResultSet();
>>> ret = res.getString(1);
>>> res.close();
>>> stmt.close();
>>> sql.close();
>>>
>>> return ret;
>>> }
>>> }
>>>
>>>
>>> # select pljava_test_internal(1);
>>> pljava_test_internal
>>> --------------------------
>>> internal test la la la 1
>>> (1 row)
>>>
>>> # select plpgsql_test_external('test');
>>> plpgsql_test_external
>>> -----------------------
>>> test from plpgsql!
>>> (1 row)
>>>
>>> # select pljava_test_external(1);
>>> ERROR: java.sql.SQLException: ResultSet is not positioned on a
>>> valid row
>>>
>>> _______________________________________________
>>> Pljava-dev mailing list
>>> Pljava-dev at pgfoundry.org
>>> http://pgfoundry.org/mailman/listinfo/pljava-dev
>>
>
> --
>
> *Lucas Madar*
> Madar Consulting Services, Inc
> lucas at mcsnw.com
> 607.592.1518
>