Database Reference
In-Depth Information
Procedural languages
SQL Server allows you to create DLLs in any language that produces CLR. These
DLLs must be loaded into the server at boot time. To create a procedure at run time
and have it be immediately available, the only choice is the built in SQL dialect, Trans-
act SQL (TSQL).
MySQL has a feature called
plugins
. One of the legal plugin types is a procedural
language. Several languages have been tooled to work with MySQL via the plugin
system, including most of the popular ones such as PHP and Python. These functions
cannot be used for stored procedures or triggers, but they can be invoked from the
common SQL statements. For the rest, you are stuck with the built-in SQL.
PostgreSQL has full support for additional procedural languages, which can be used
to create any legal entity in the database that can be created with PL/pgSQL. The
language can be added (or removed) from a running version of PostgreSQL and any
function defined using that language may also be created or dropped while Post-
greSQL is running. These languages have full access to PostgreSQL internal func-
tions and all data entities that the calling user has permission.
There are many of these plugin language extensions available for PostgreSQL. I have
used the ones for PHP, Python, bash, and PL/pgSQL. Yes, that means that the stand-
ard language for PostgreSQL is also installed and managed using the same exten-
sion system as any other language.
This brings us to the point that we have more developers available for PostgreSQL
than you might have originally thought. Software developers are not required to learn
a new development language to write stored procedures. They can extend Post-
greSQL with the language of choice, and continue to code in the manner and work-
flow that they choose.
Lesson learned
: There are no second class citizens in the PostgreSQL development
community. Anyone can code in (almost) any language they choose.
Third-party tools
: A frequent comparison point among the database platforms is the
number of third-party applications available. I'm not so sure that the total number of
them matters as much as the existence of applications you actually need.
