On Mon, 2003-10-27 at 15:17, scott.marlowe wrote:
On 27 Oct 2003, Klaus P. Pieper wrote:
Hi,
does anybody out there have experience with the several PL's which are
available for PostgreSQL? I am currently evaluating several databases
(commercial as well as free & open source) for a new project and would
just like to hear some feedback.
PL/Java seems to be developed by a fairly small team - no updates on
their website since December 2002 (and even what's available on this
web site is not very encouraging to use PL/Java on a production
system). Does anybody use PL/Java?
What about PL/Python or PL/Ruby? Any experience with these two
implementations on a production system? Are there any PostgreSQL
specifics or limitations? Significant differences between these two
languages?
The two or three "heavy lifters" of the pl/xx set are plSQL, which is just
SQL wrapped in a function, and fairly limited, PL/pgSQL, which is
Postgresql's implementation that is fairly similar to Oracle's plsql, and
C functions. Generally, functions written in C are the fastest, but also
require the most care, as improperly written ones can cause individual
backends to crash.
pl/pgsql is a good compromise between these two and probably has the most
stored procs written in it.
pl/python just lost its trusted status, so that might be an issue until
they get rexec fixed.
I haven't used plruby.
pl/R is a nice choice if you're doing statistical analysis
pl/tcl has gotten good marks
pl/php seems to be maturing quickly, and will likely get a good shaking
down in the next 6 months
I'm not sure what the status of pl/J is, but I can't imagine a pl needing
a lot of maintenance once the bugs are worked out, as it's mostly just
glue code. I.e. the real updates come in Java and Postgresql
individually, not in the interface layer.
If you're looking to get started, plpgsql is a very good starting point.
If you need a trusted language (i.e. sandboxed) plpgsql, pl/J, pl/php,
pl/perl, and pl/tcl are good choices to explore, as they can all run in a
trusted version.
I'm not as bullish on all of these as scott, heres my break down of
things I'd want to look at in a pl language (in no particular order):
1. trusted/untrusted status
this controls how what privileges the pllang will have, like opening
sockets, reading the filesystem, etc... there are pros and cons for each
type, but for maximum flexibility you want both.
2. trigger support
this means you can write triggers directly in the pllang in question.
this can be worked around but it's a sign of maturity for the pllang
imho
3. able to query against the database
some pllangs can not execute queries against the database, but are
limited to only being able to perform functions "native" to the lang.
4. is it part of the main distribution
this one is really unfair to some of the languages, like plr which has a
great maintainer in Joe Conway, but if a language isn't in the core
distribution, it's likely not getting banged on like the ones that are
in core. unless i was really familiar with the base language in question
I'd stick to what's in the main packages.
none of these are deal killers depending on what your use is for them,
but its a good way to size up the different choices. in my production
system i would say my current breakdown is 25% sql funcs, 70% plpgsql,
and 5% pltcl, with a couple of plc's from contrib I have modified. I've
also used plpython, plperl, and plphp at least somewhat in passing, but
none of those offer me things I don't already have in my main 3 (and
note I've never written a tcl program in my life)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
ma*******@postg resql.org 
