From: lucas at mcsnw.com (Lucas Madar)
Date: Mon, 10 Dec 2007 17:29:06 -0800
Subject: [Pljava-dev] Calling pl/pgsql functions from pl/java?
Message-ID: <475DE7E2.3000506@mcsnw.com>

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