Thread: Returning arbitrary row sets from a function

Returning arbitrary row sets from a function

From
"Gerard Mason"
Date:
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


Re: Returning arbitrary row sets from a function

From
"Gerard Mason"
Date:
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


Re: Returning arbitrary row sets from a function

From
Joe Conway
Date:
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




Re: Returning arbitrary row sets from a function

From
Joe Conway
Date:
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



Re: Returning arbitrary row sets from a function

From
"Gerard Mason"
Date:
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


Re: Returning arbitrary row sets from a function

From
"Gerard Mason"
Date:
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