In postgresql – selecting maximum for each group, we saw the actual stored function.  That was the easy part.  Then there was dealing with Postgresql and JDBC.  I encountered a few surprises while doing this.

Calling the stored function

I expected to use JDBC’s CallableStatement since I was calling a stored procedure.  Nope.  The proper way to call it is:

PreparedStatement stmt = conn.prepareStatement("select * from highlighted_topic_per_forum(?) limit ?");

stmt.setString(1, formattedDate);
stmtp.setInt(2, limit);

Passing a timestamp

Originally, I was passing a timestamp into the stored function by calling stmt.setTimestamp() and a type of “timestamp without time zone”.  Try as I may, postgresql didn’t like this.  It complains $1 not found.  I eventually decided to pass the timestamp as a string.

Passing an array

I was originally planning to pass the category ids via JDBC.  Given what what I experienced with timestamps, I wasn’t so enthusiastic about trying this on an application I work on in my free time.  When I saw the advice online to extend the Array class, I decided to hard code the two category ids.