javapostgresqlstored-proceduresjdbccallable-statement

How to get dynamic resultset on calling a postgresql function


I have written a stored procedure in PostgreSQL with an if and else if condition. In each condition having different queries that return different results. How can I access the result in Java using resultset.getString() method?

Stored procedure:

CREATE FUNCTION public."testDynamicReturn"(_tagId character varying)
    RETURNS ??? -- what should be written in the place of ???
    LANGUAGE 'plpgsql'

AS $BODY$
BEGIN

        IF _tagId == 'stu' then 
            RETURN query
            SELECT "StudentRollNo.","StudentName","GuardianName" FROM public."DetailsOfStudents";
        ELSE if _tagId == 'teach' then
            RETURN query
            SELECT "TeacherName","TeacherAddress" FROM public."DetailsOfTeachers";

END;
$BODY$;

Java code:

    public static void main(String[] args) {
        Connection connection = DBUtility.getDBConnection();
        String sp = "{ call \"testDynamicReturn\"(?)}";

        try {
            CallableStatement statement = connection.prepareCall(sp);
            statement.setString(1, "stu");
            statement.execute();

            ResultSet resultSet = statement.getResultSet();
            while (resultSet.next()) {
                String a = resultSet.getString(???);
                System.out.println(a);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

If I send "stu" as stored procedure parameter then result will be:

--------------------------------------------
StudentRollNo.| StudentName | GuardianName |
--------------------------------------------
15600223596   | John        |   Doe        |
--------------------------------------------

And if I send "teach" as stored procedure parameter then result will be:

------------------------------
TeacherName | TeacherAddress |
------------------------------
Adam        | New york       |
------------------------------

Please answer how to achieve the above.


Solution

  • Here is the function:

    CREATE OR REPLACE FUNCTION  public.testDynamicReturn(_tagId character varying)
        RETURNS TABLE (out_rollNum character varying, out_name character varying, out_gaurdian character varying) 
        AS 
        $func$
        BEGIN
         IF _tagId = 'stu' then 
                   --validate your select, ensure it works independently
                   RETURN QUERY  SELECT "StudentRollNo.","StudentName","GuardianName" FROM public."DetailsOfStudents";  
                   END IF;
             IF _tagId = 'teach' then 
                   --validate your select, ensure it works independently
                   RETURN QUERY  SELECT "StudentRollNo.","StudentName", CAST (null AS character varying) "GuardianName" FROM public."DetailsOfStudents";
                   END IF;
    
        END
        $func$  LANGUAGE plpgsql;
    

    Now the code to call this dynamic function:

     PreparedStatement statement = conn.prepareStatement("select * from public.testDynamicReturn('stu')");
    
    ResultSet resultSet = statement.executeQuery();
    
    while (resultSet.next()) {
     //can give column name
     String rollNum= resultSet.getString("out_rollNum");   
      String name= resultSet.getString("out_name");   
      //will be null for 'stu'but you already know
      String gaurdian= resultSet.getString("out_gaurdian");    
    }