Re: Returning arbitrary row sets from a function

Back to my original objective, with my shiny new 7.4 installation I've
created a view, then a function that returns that view's rowtype. All goes
well in interactive psql (or at least it does once I tried "select * from
api.get_organisations_display( 1 )" rather than the "select
api.get_organisations_display( 1 )" that I was expecting to use).

However, the client will be calling via jdbc. Now of course, first thing I
did was try just replacing the original client sql with the version that I
found to work in psql above; and, very nicely, that also worked in the
client. However that takes the form of a java.sql.PreparedStatement, whereas
I presume I should be using a java.sql.CallableStatement instead. But when I
try this:

con = dataSource.getConnection();
CallableStatement stmt = con.prepareCall( "{call
api.get_organisations_display( ? )}" );
stmt.setInt( 1, 1 );
stmt.execute();
ResultSet rs = stmt.getResultSet()

then I get this error at the execute() line:

java.sql.SQLException: ERROR:  cannot display a value of type record


Now I'm not the only one to have come across this problem, see
http://archives.postgresql.org/pgsql-jdbc/2003-03/msg00143.php for example,
but it's interesting to note that there IS in fact a benefit to doing things
this way, which is that you can restrict access rights to your tables while
also insulating the client from changes to the implementation -- the
traditional benefits of an API in fact. In this context, using a
CallableStatement would be the database-independent way of doing it, whereas
doing a "select * from api.get_organisations_display( 1 )"-style prepared
statement might make the client dependent on a postgresql back end.

This is a bit of a ramble rather than a question requiring an answer. What I
think I'll do is use views for selects, which will give me almost all the
same benefits, maybe use the "select * from api.function( x, y )" approach
for complicated stuff, and use functions for updates, inserts and deletes.


Cheers,
Gerard.

_________________________________________________________________
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile