From: jeffreylyon at mac.com (Jeffrey Lyon)
Date: Mon, 10 Dec 2007 22:01:14 -0500
Subject: [Pljava-dev] Calling pl/pgsql functions from pl/java?
In-Reply-To: <475DF66A.4030400@mcsnw.com>
References: <475DE7E2.3000506@mcsnw.com>
	<141A53EE-DEE3-4C80-B4EF-F5A888F6F568@mac.com>
	<475DF66A.4030400@mcsnw.com>
Message-ID: <B98BC88F-2AB2-4C65-90A9-35676AF4F1A9@mac.com>

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
>