Re: little off-topic: stored procedures

A late follow up to this.

Accepting the point about portability one of the great things about
PostgreSQL is that it is free _and_ scalable. I think I'd happily use
it on just about any project.

Given that using stored procs is great. However, don't fall into the
trap of creating rules about what should go into a stored proc and
what should go into java.

Some people suggest that business logic should stay in java and some
people suggest stored procs are a good place for it. Put those 2
sorts of people in a room and you'll have an entertaining half hour.


I prefer to follow the needs of performance. Things that will perform
better on the server side should go into stored procs. Things that do
a lot of memory allocation, or SAX processing, need to be represented
as objects in java.

It soons becomes apparent when you're coding if you've made a
mistake. For example, whilst writing a stored proc the other day, I
found myself wishing that PL had a Hashtable data structure. I sat
back for a moment and thought about what I was doing and realised I
was approaching the problem from the wrong end. I re-wrote the code in
java. Even including making the mistake the code only took about 30
minutes to write and debug.

One tip: encapsulate all your stored procs in static methods
(maybe in a single class if you have few procs). Doing that means you
_can_ rewrite them to java quickly (or hack the internals) if you have
to.



btw I don't agree about the idea of using Java as a stored proc
language. I've investigated this and it's a royal pain in the
neck. Java is not wordy enough to deal with *a lot* of SQL
effectively. I am investigating alternatives to PL though, Lisp seems
to me particularly interesting because of what might be possible with
the idea of resultsets as closures.


Nic Ferrier