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.
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");
}