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(dot)bishop(at)canonical(dot)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-----