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