 |
|
EnterpriseDB: Other Programming Languages
Oracle Tips by
Burleson
|
EnterpriseDB inherits the PostgreSQL ability to add languages to the
core database product. These languages run inside the database as
opposed to traditional languages that run externally and connect as
needed. The languages that I cover below run inside the database
itself. If the database is not running, programs in these
languages cannot be executed.
The
language architecture of EnterpriseDB requires a dynamically loadable
library to execute. This would mean a DLL in windows or an SO
file in Linux. Functions written inside the database would make
calls to this external library to execute.
Because
these languages are full featured languages and because they can make
use of the entire language facility, EnterpriseDB implements what is
known as SAFE (TRUSTED) and UNSAFE
(UNTRUSTED)
languages. A safe language is not allowed to interact with the
operating system. For example, it cannot access the file system
or memory internals. TCL and PERL, both of which I briefly cover
below, have both SAFE and UNSAFE variants.
PL/pgSQL,
however, has only a SAFE variant.
A major
point
to
note is that EnterpriseDB stores procedural code as strings.
That means that the compile time error checking is only as good as the
tool doing the parsing. What that means, is that even more than
ever, just because it compiled does not mean it's going to work.
There are many errors you will only find at runtime.
Installing a Language
Creating
a new language for EnterpriseDB is completely outside the scope of
this book. However, there are many languages already created.
To add a language to EnterpriseDB, you must first get the wrapper DLL
supplied by EnterpriseDB and you must have the language being called
installed.
PL/pgSQL
is a special case as it was written specifically for EnterpriseDB and
PostgreSQL. EnterpriseDB always comes with SQL, SPL and PL/pgSQL
pre-installed. For any other language, contact EnterpriseDB Support
and request the wrapper libraries for your OS platform and version.
I was able to get TCL and Perl for both Windows and Linux by
requesting them from support.
The
requirements for installing a particular language are dependant on
that language. You can get many languages from ActiveState.com.
That is where I got my versions of TCL and PERL. If you are
unsure about which language to install, call EnterpriseDB support.
If you are unsure how to install, call the creator of your language
for support, not EnterpriseDB support.
Once you
have the language installed on your machine and you have received the
wrapper DLLs, copy the DLLs to the <EnterpriseDB Home>/dbserver/lib
directory. On my windows box, that directory is
c:\EnterpriseDB\8.1\dbserver\lib.
The
CREATELANG command is used to add the language to your database.
To run this command, open a command shell and navigate to your
EnterpriseDB bin directory (on that same windows PC as above, that
would be: c:\EnterpriseDB\8.1\dbserver\bin). The easy
syntax is:
CREATELANG –-username=<enterprisedb super user> <language> <database>
For
example, when I installed the unsafe language pltclu to my edb
database, I use my db user, enterprisedb, to do so. The command
I used was:
createlang --username=enterprisedb pltclu edb
The
command will prompt you for your password. If you get errors,
that means that you probably don't have the language installed
correctly. Make sure that your language is installed and can be
run and that you have the directory containing your language DLLs in
the path. I also found that adding my EnterpriseDB bin directory
to the path helped.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
