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.

__________________________________________________ _______________
Tired of 56k? Get a FREE BT Broadband connection
http://www.msn.co.uk/specials/btbroadband


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"Gerard Mason" <gerardmason (AT) hotmail (DOT) com> writes:

That seems to be your problem ;-)

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 (AT) postgresql (DOT) org)