PostgreSQL CREATE LANGUAGE statements.

Over the last week, as part of my work, I needed to install, enable, and test 3 different procedural modules for PostgreSQL: PL/Tcl, Pl/Perl, and PL/pgSQL. By far the easiest was PL/pgSQL since it comes enabled by default on the database, a user simply needs to run:
CREATE LANGUAGE plpgsql;
After connecting to the database that you want it installed in, e.g., psql -U user.

This doesn’t work if your other languages aren’t installed into the database, you’ll get this error:

Same with PL/Tcl.
At this point you can spend a couple hours reading through this:

http://www.postgresql.org/docs/8.3/interactive/xplang-install.html

And this:

http://www.postgresql.org/docs/8.3/static/sql-createlanguage.html

You’ll get pretty much no where since the only information about installing a procedural language is one that is already installed (very helpful, thanks).

The documentation does allude to some of the helpful pieces in getting the procedural language installed but it doesn’t explicitly state what you are supposed to do. I’m going to do that now. You need to run this command:

From this command you can see that we currently have 3 languages installed PL/pgSQL, PL/C, and an internal language. Note: PL/Tcl and PL/Perl are not installed here, so they can’t be used with the CREATE LANGUAGE command. As long as you’ve installed the correct RPMs or .deb files you will be able to see them here:

This is a list of all of the languages available as a template in your database. Here’s where the docs stop being helpful at all: There is nothing telling you how to manually install pl/tcl or how to manually install pl/perl into a postgres database. Sure, the docs say, if you are installing PL/pgSQL then you can do x…but x doesn’t work the same for PL/Tcl or PL/Perl. There are pages of docs on the handler calls and validator information (this plus a couple thousand lines of C and you’ll have a great validator).

Here’s really how to do it:

CREATE LANGUAGE pltcl;
CREATE LANGUAGE plperl;

That’s it. Because these languages are installed in the pg_pltemplate they only require the create language command so after logging into your database and running the 2 commands above you’ll be able to add them to databases as the user like this:

So, now, look through the docs again – yes _all_ of them. I’ll wait. OK, back? Good, you may say “All of these steps are there; you should have just read more carefully”. True maybe I should have read more carefully, I spent hours going over the documentation and searching, BUT it was on different pages and scattered throughout the entire documentation set for PL and each of the different PLs.

A quick recap of what you need to do to install PL/Tcl manually:

1. Run: select * from pg_language;
2. See if your language is already installed.
3. If not, then you need to make sure it is installed in the system.
4. To see if it is installed in the system run: select * from pg_pltemplate;
5. If your modules are there run: CREATE LANGUAGE pltcl;
6. Log in as the database user and run create language pltcl; to install it for that database.

A quick recap of what you need to do to install PL/Perl manually:

1. Run: select * from pg_language;
2. See if your language is already installed.
3. If not, then you need to make sure it is installed in the system.
4. To see if it is installed in the system run: select * from pg_pltemplate;
5. If your modules are there run: CREATE LANGUAGE plperl;
6. Log in as the database user and run create language plperl; to install it for that database.

That’s it. You’re done. You can go about your business.