Re: Experience with PL/xx?

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.