Re: PL/Pgsql or PL/Python?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Wolfgang Keller wrote:

| I was wondering whether it is worth for me to learn PL/Pgsql, given the
| support of Python in Postgresql. The latter (Python) is the only
| programming language I've learned voluntarily in my whole life, i.e.
| without being obliged to do so, you know. ;-)
|
| So, what things can be done in PL/Pgsql which can't be done in PL/Python
| (and vice-versa)? And, how do both compare in terms of runtime efficiency
| (speed/memory)?
|
| Apart from typical "ERP-like" work, the applications I am planning to use
| Postgresql for involve storage and (numeric) processing of quite
| significant amounts of (binary) measurement data. Specifically, "hacking"
| long streams of binary data fed into a database in batch mode into
| individual OPC-conform triplets, among others. So I thought the numeric
| module and iterator feature in Python would be quite useful...

There is one limitation in particular to to using PL/Python instead of
PL/pgSQL:

~  - If a user has access to create PL/Python stored procedures,
~    they have full access to the host the PostgreSQL is running on
~    as the user it is running as.

~  - The API for executing database queries or calling other server side
~    procedures is fairly primative.

However, there are also advantages to using PL/Python (or PL/Perl for
that matter):

~  - You are less likely to be the only person around who can maintain
~    the code.

~  - You can share the same code on the server side and on the client
~    side (so the same Python code can be used to enforce your column
~    constraints as is used in your form validation, for example).

~  - You can test your code outside of PostgreSQL using the standard
~    Python test harnesses.

~  - You have access to the Python standard library and any installed
~    libraries, so you can avoid reinventing a lot of wheels.

I don't think anyone could tell you which would end up being faster or
use less memory - I expect it would depend on whether the savings using
numeric or other libraries outweighs the overheads involved in
translating data between Python and PostgreSQL formats.

- --
Stuart Bishop <stuart.bishop@canonical.com>   http://www.canonical.com/
Canonical Ltd.                              http://www.ubuntulinux.com/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFCKFlHAfqZj7rGN0oRAtTAAJ0R6x1/k2S/kzOzeWgwb42Yq98HvgCfYprE
qUWC+h19qGn6gK3SsgbmpM8=
=xzjX
-----END PGP SIGNATURE-----