Return rows from a PL/pgSQL function

Question

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'

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?

Now I use this:

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);

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

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

Line 25 is: proc.execute();

Answer 1

Proper function definition

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;

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.

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

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

Call the function in SQL

SELECT * FROM get_geom_difference();

Do it via JDBC

I quote the manual here

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.

Answer 2

In your definition your function returns nothing RETURNS void. If you want num data type just change to RETURNS numeric. Also, To make it worked, perform select into some numeric variable SELECT INTO var and add return var to the end of function.

Answer 3

Although Erwin provided a great answer, I thought I'd share a version in which the function takes params, and I'm using the new postgres return query syntax within the body of the function. The difference is that it seems to set params you have to use the PreparedStatement interface rather than the higher level Statement. here's my pg function

CREATE OR REPLACE FUNCTION gettools(_userid bigint)
  RETURNS table(id bigint,itemname character varying(250)) AS
$BODY$

begin   
    return query 
    select distinct a.id, a.itemname 
    from tool a inner join tooltie b
     on a.id = b.toolid 
     and b.userid=_userid;
end;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 100;

and here's my java that simply fills a map with data

  private static final String GET_DATASOURCES = "select * from getdatasources(?)";

    public Map<Long, String> getAuthDataSources(Long userid) {
        Map<Long, String> auths = new TreeMap<>();
        try {
          if (connection == null || connection.isClosed()) {
            init();
          }
          PreparedStatement cs = connection.prepareStatement(GET_DATASOURCES);
          // this is where to set the param ? for the function
          cs.setLong(1, userid);
          connection.setAutoCommit(false);        
          ResultSet result = cs.executeQuery();

          while (result.next()) {
            Long id = result.getLong(1);
            String itemname = result.getString(2);
            auths.put(id, itemname);
          }
          cs.close();
          connection.commit();
          connection.setAutoCommit(true);
          connection.close();
        } catch (SQLException ex) {
          LOG.error("error getting data sources", ex);

        }
        return auths;
      }

Hope this helps someone.