Thread: Returning arbitrary row sets from a function
I would like to be able to deny client applications access to tables, and insulate the database through an api of functions. Updater, inserter and deleter functions look to be easy enough, but I am having a problem with rowset-returning functions: how do I declare return types that are a SETOF rows containing columns from an arbitrary query? For example, suppose I want a function that returns a display-formatted organisation, by joining with countries so that the country appears as a name rather than an integer. What is currently happening is that the client is sending the query: SELECT o.name, c.name AS country, o.phone, o.mobile, o.fax, o.email, o.website FROM organisations o, countries c WHERE c.id = o.country What I'd *like* to happen is that the client calls, say, get_org_long() and retrieves the same data. But what would the return type be? The only way I can think to do it at the moment would be to create a view and return a setof that view's rowtype, but that seems to be using a sledgehammer to crack a nut. Also my first attempt at defining a test function that just returns rows from the organisations table fails with the message: ERROR: parser: parse error at or near "%" It looks like this: CREATE FUNCTION gems_test() RETURNS SETOF organisations%ROWTYPE AS That seems to be what the documentation is suggesting the return type should be (the examples are very incomplete!), but it doesn't work (7.1.3, and I can't upgrade without buying a new machine, which I don't want to do just yet). Cheers, Gerard. _________________________________________________________________ Hotmail messages direct to your mobile phone http://www.msn.co.uk/msnmobile
We appear to be having the same problems posting ;-) Well, is it possible to run two different versions of postgresql side-by-side on the same machine? I don't want to end up with a non-working database just because I've made some stupid mistake installing a new version, and then find that I can't revert back to the old one either. Is it at least possible to have two different versions installed side-by-side, even if you can only have one running at a time? Cheers, Gerard. >From: Tom Lane <tgl@sss.pgh.pa.us> >To: "Gerard Mason" <gerardmason@hotmail.com> >CC: pgsql-novice@postgresql.org >Subject: Re: [NOVICE] Returning arbitrary row sets from a function Date: >Tue, 16 Dec 2003 09:28:04 -0500 >Received: from mc5-f1.hotmail.com ([65.54.252.8]) by mc5-s6.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6713); Tue, 16 Dec 2003 06:33:44 -0800 >Received: from noon.pghoster.com ([64.246.0.64]) by mc5-f1.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6824); Tue, 16 Dec 2003 06:33:43 -0800 >Received: from svr1.postgresql.org ([200.46.204.71] helo=postgresql.org)by >noon.pghoster.com with esmtp (Exim 4.24)id 1AWGCE-00055p-BQ; Tue, 16 Dec >2003 08:29:10 -0600 >Received: from localhost (neptune.hub.org [200.46.204.2])by >svr1.postgresql.org (Postfix) with ESMTP id 5D1F5D1DBD0for ><pgsql-novice-postgresql.org@localhost.postgresql.org>; Tue, 16 Dec 2003 >14:28:33 +0000 (GMT) >Received: from svr1.postgresql.org ([200.46.204.71]) by localhost >(neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id >43887-04 for <pgsql-novice-postgresql.org@localhost.postgresql.org>; Tue, >16 Dec 2003 10:28:05 -0400 (AST) >Received: from sss.pgh.pa.us (unknown [192.204.191.242])by >svr1.postgresql.org (Postfix) with ESMTP id 8C331D1DC11for ><pgsql-novice@postgresql.org>; Tue, 16 Dec 2003 10:28:01 -0400 (AST) >Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])by >sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hBGES419021036;Tue, 16 Dec >2003 09:28:04 -0500 (EST) >X-Message-Info: pdGgd64CkwbpMDe6De87M4BVeULv8cLfHTWRrON5Om0= >X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org >In-reply-to: <BAY7-F113qicRfZ7iHY0003b340@hotmail.com> References: ><BAY7-F113qicRfZ7iHY0003b340@hotmail.com> >Comments: In-reply-to "Gerard Mason" <gerardmason@hotmail.com>message dated >"Tue, 16 Dec 2003 14:18:54 +0000" >Message-ID: <21035.1071584884@sss.pgh.pa.us> >X-Virus-Scanned: by amavisd-new at postgresql.org >X-Mailing-List: pgsql-novice >Precedence: bulk >X-AntiAbuse: This header was added to track abuse, please include it with >any abuse report >X-AntiAbuse: Primary Hostname - noon.pghoster.com >X-AntiAbuse: Original Domain - hotmail.com >X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] >X-AntiAbuse: Sender Address Domain - postgresql.org >Return-Path: pgsql-novice-owner+M9061@postgresql.org >X-OriginalArrivalTime: 16 Dec 2003 14:33:43.0294 (UTC) >FILETIME=[9B0DD9E0:01C3C3E1] > >"Gerard Mason" <gerardmason@hotmail.com> writes: > > That seems to be what the documentation is suggesting the return type >should > > be (the examples are very incomplete!), but it doesn't work (7.1.3, > ^^^^^ > >That seems to be your problem ;-) > > > and I can't upgrade without buying a new machine, which I don't want > > to do just yet). > >Surely you can upgrade. If the problem is lack of RPMs for whatever OS >you are running, you could just compile Postgres from source. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) _________________________________________________________________ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger
Gerard Mason wrote: > how do I declare return types that are a SETOF rows containing > columns from an arbitrary query? Declare the function to return "setof record" and then specify the rowtype at runtime. See: http://techdocs.postgresql.org/guides/SetReturningFunctions for a good tutorial. > For example, suppose I want a function that returns a > display-formatted organisation, by joining with countries so that the > country appears as a name rather than an integer. What is currently > happening is that the client is sending the query: > > SELECT o.name, c.name AS country, o.phone, o.mobile, o.fax, o.email, > o.website FROM organisations o, countries c WHERE c.id = o.country > > What I'd *like* to happen is that the client calls, say, > get_org_long() and retrieves the same data. But what would the return > type be? The only way I can think to do it at the moment would be to > create a view and return a setof that view's rowtype, but that seems > to be using a sledgehammer to crack a nut. Why? At some point you have to let the planner/optimizer know what the data types are. There's no way around it. If you don't want to use a view, see CREATE TYPE (which is more appropriate anyway): http://www.postgresql.org/docs/current/static/sql-createtype.html In any case, why not just use a view -- it doesn't sound like you need a function, at least not from your example. > Also my first attempt at defining a test function that just returns > rows from the organisations table fails with the message: > > ERROR: parser: parse error at or near "%" > > It looks like this: > > CREATE FUNCTION gems_test() RETURNS SETOF organisations%ROWTYPE AS I can't see where you'd get that impression. > That seems to be what the documentation is suggesting the return type > should be (the examples are very incomplete!), but it doesn't work > (7.1.3, and I can't upgrade without buying a new machine, which I ^^^^^ That seems to be your problem. You need to be on 7.3 at least. Joe
Gerard Mason wrote: > Well, is it possible to run two different versions of postgresql > side-by-side on the same machine? I don't want to end up with a > non-working database just because I've made some stupid mistake > installing a new version, and then find that I can't revert back to the > old one either. Is the original installed from source, or from some sort of package? If the latter, reverting should be easy. Just make sure you save a copy of the old data directory off to the side somewhere (taken *after* dumping and shutting down the old postmaster). > Is it at least possible to have two different versions installed > side-by-side, even if you can only have one running at a time? You can, but it's tricky to get it right. Joe
Thanks for those responses. Well in a fit of what I like to think was professional courage (but which was probably more like simple lunacy) I downloaded 7.4, read the install docs, typed "./configure" and "make" (very exciting!), err..., downloaded GNU bison, typed "./configure" and "make" (still exciting!), err..., downloaded GNU readline, typed "./configure" and "make" (yawn!), typed "make install" (very exciting again!), and got something that looked like a postgresql 7.4 installation in /usr/local. Hurrah! This was fine, because the machine is running Mandrake 8.1, and the pre-packaged postgresql is spread out in /usr/bin, /usr/lib, /var/lib, and so on, so I didn't have to delete it. A bit later and I was hacking away at /etc/rc.d/init.d/postgres, which at least does run 7.4 when invoked from the command line, though I haven't actually rebooted yet. The only minor annoyance was that psql, createdb, pg_ctl and so on were binaries in /usr/bin, so I renamed them to *.bak and linked to the ones in /usr/local/pgsql/bin -- I hope I've got them all, I wouldn't want to be running a 7.1.2 (yes, not 7.1.3 as I thought) binary against a 7.4 database... Thanks for your encouragement, Gerard. _________________________________________________________________ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger
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