从PL/pgSQL函数返回行 [英] Return rows from a PL/pgSQL function

问题描述

我在PostgreSQL中有一个过程:

I have a procedure in PostgreSQL:

CREATE OR REPLACE FUNCTION get_geom_difference()
RETURNS void AS
$$
BEGIN
SELECT filedata.num,st_area(ST_Difference(ST_TRANSFORM(filedata.the_geom,70066),filedata_temp.the_geom))
FROM filedata, filedata_temp
Where filedata.num=filedata_temp.num

end;
$$
LANGUAGE 'plpgsql'

我用Java调用它，想要获得此过程的结果.如何更改此过程以使其可能得到结果?以及如何在JDBC中使用它?

I call it in Java and want to get result of this procedure. How to change this procedure to make it possible get a result? And how to work with it in JDBC?

现在我用这个:

Integer fileId;
Class.forName("org.postgresql.Driver");
Connection connect= null;
connect = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgis","postgres","123456");
java.sql.CallableStatement proc =  connect.prepareCall("{?=call get_geom_difference()}");
proc.registerOutParameter(1, java.sql.Types.Integer);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next()) {
fileId=r.getInt("num");
}
proc.close();
connect.close();
out.println(fileId);

但是当我尝试在JDBC中调用过程时，我会得到

But When I try to call the procedure in JDBC I get

错误org.apache.jasper.JasperException:发生异常 在第25行处理JSP页面/commit_changes.jsp

error org.apache.jasper.JasperException: An exception occurred processing JSP page /commit_changes.jsp at line 25

第25行是:proc.execute();

推荐答案

正确的函数定义

CREATE OR REPLACE FUNCTION get_geom_difference()
  RETURNS TABLE (num mumeric, my_area geometry) AS
$BODY$
   SELECT f.num
         ,st_area(ST_Difference(ST_TRANSFORM(f.the_geom, 70066), t.the_geom))
   FROM   filedata f
   JOIN   filedata_temp t USING (num);
$BODY$
LANGUAGE sql;

要返回复合类型的SET(两列)，必须相应地声明该函数. RETURNS TABLE 是最方便的方法.

You are returning a SET of a composite type (two columns), you have to declare the the function accordingly. RETURNS TABLE is the most convenient way to do this.

请确保对查询中的列名进行表限定，以免与同名的OUT列冲突.

Be sure to table-qualify the column names in the query so they do not conflict with OUT columns of the same name.

您可以为此使用 language SQL函数基本查询(或者您可以只执行原始SQL)，不需要plpgsql.

You can use a language SQL function for this basic query (or you could just execute the raw SQL), no need for plpgsql.

SELECT * FROM get_geom_difference();

通过JDBC完成

我在此处

不应将通过集合返回数据的函数调用 CallableStatement接口，但应使用常规接口 语句或PreparedStatement接口.

Functions that return data as a set should not be called via the CallableStatement interface, but instead should use the normal Statement or PreparedStatement interfaces.

我还从网站上获取了此示例，并对其进行了修改:

I also took this example from the site and adapted it:

Statement stmt = conn.createStatement();
stmt.execute(" <function definition from above goes here> ");
ResultSet rs = stmt.executeQuery("SELECT * FROM get_geom_difference();");
while (rs.next()) {
    // do something
}
rs.close();
stmt.close();

您还可以使用反射器.阅读更多手册.

You could also use a refcursor. Read more in the manual.

这篇关于从PL/pgSQL函数返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！