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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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.


__________________________________________________ _______________
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Gerard Mason wrote:
Declare the function to return "setof record" and then specify the
rowtype at runtime. See:
http://techdocs.postgresql.org/guide...rningFunctions
for a good tutorial.

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/curre...reatetype.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.


I can't see where you'd get that impression.


That seems to be your problem. You need to be on 7.3 at least.

Joe




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Gerard Mason wrote:
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).

You can, but it's tricky to get it right.

Joe



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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


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

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

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...3/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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly