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.
Pingback: postgresql – selecting maximum for each group | Down Home Country Coding With Scott Selikoff and Jeanne Boyarsky