executing pl/pgsql function in postgre and java
CREATE OR REPLACE FUNCTION squre_root(integer)
RETURNS double precision AS
$BODY$
BEGIN
return SQRT($1);
END; $BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
ALTER FUNCTION squre_root(integer) OWNER TO hede;
to execute this procedure in pgadmin : select squre_root( 9 );
import java.sql.*;
public class PlSqlTest {
public static void main(String args[]) {
CallableStatement stmt = null;
int a=9;
System.out.println(”\nArray Parameter Test\n”);
try {
Class.forName(”org.postgresql.Driver”);
Connection conn = DriverManager.getConnection(”jdbc:postgresql://localhost/hede”, “hede”,”hede”);
conn.setAutoCommit(false);
stmt = conn.prepareCall(”{?= call squre_root(?)}”);
stmt.registerOutParameter(1, java.sql.Types.DOUBLE);
stmt.setInt(2, a );
stmt.execute();
double result = stmt.getDouble(1);
System.out.println(”\nThe result is ” + result);
conn.close();
}
catch (SQLException se) {
System.out.println(”\nA SQL exception occurred.”);
System.out.println(”\nError code: ” + se.getErrorCode());
System.out.println(”\nSQL state: ” + se.getSQLState());
se.printStackTrace();
}
catch (Exception e) {
System.out.println(”\nSome other exception occurred.\n”);
e.printStackTrace();
}
}
}