I have the following postgresql Function:
CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE)
RETURNS SETOF message
AS $$
DECLARE
sql varchar;
BEGIN
sql := 'SELECT * FROM message WHERE (receiver_id IS NULL OR receiver_id = $1)';
IF qid IS NOT NULL THEN
sql := sql || ' AND queue_id = $2';
END IF;
IF ctxt IS NOT NULL THEN
sql := sql || ' AND context = $3';
ELSE
sql := sql || ' AND context IS NULL';
END IF;
IF $4 IS NOT NULL THEN
sql := sql || ' AND sender_id = $4';
END IF;
sql := sql || ' ORDER BY';
IF byTimeStamp THEN
sql := sql || ' arrive_time ASC';
ELSE
sql := sql || ' priority DESC';
END IF;
sql := sql || ' LIMIT 1';
RETURN QUERY EXECUTE sql
USING messageFor, qid, ctxt, messageFrom;
END;
$$
LANGUAGE plpgsql;
Now, the above function works fine but the resultSet does not contain column names (for example: a simple SELECT statement returns results with column names). I need to be able to access the result of the above function in Java code as:
Connection con;
PreparedStatement ps = con.prepareStatement("select readMessage(1, 1, 1, 1, true))";
ResultSet rs = ps.executeQuery();
rs.getInt(<column-name>)
instead of
rs.getInt(<column-index>)
for more readable code. How can I modify my postgresql FUNCTION to achieve this?