Re: Returning arbitrary row sets from a function

From: "Gerard Mason" <gerardmason(at)hotmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Returning arbitrary row sets from a function
Date: 2003-12-17 01:30:41
Message-ID: BAY7-F373gwhDCvY3PU0003ddfd@hotmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Browse pgsql-novice by date

  From Date Subject
Next Message joseph speigle 2003-12-17 10:25:13 basic function not working
Previous Message Gerard Mason 2003-12-16 21:17:41 Re: Returning arbitrary row sets from a function