Saturday, September 15, 2007

Programming Postgres

This is just a quick overview of the availability of programming for Postgres. With Oracle, your choice in the database is Java or PL/SQL. In general, PL/SQL is the preferred language. One of the things that attracted me to Postgres was the ability to choose one of many languages to program in. I usually choose PL/pgSQL but if I can't do something with that, it's nice to be able to fall back to TCL, Perl or one of the many other languages supported by Postgres. Here is a (incomplete, I'm sure) list of supported languages:

  • PL/pgSQL - Standard Procedural Language for Postgres
  • PL/TCL - TCL, Tool Command Language, easy to learn but powerful
  • PL/Perl - Practical Extraction and Report Language, has grown way beyond its origins
  • PL/Java - Java, meh
  • PL/PHP - PHP: Hypertext Preprocessor, over hyped language
  • PL/Python - Object Oriented, dynamic language. Almost as over hyped as PHP
  • PL/R - Statistical Language, never used it.
  • PL/Ruby - Great language. Over hyped maybe but is my current favorite open source language
  • PL/Scheme - Lispy language, never used it
  • PL/sh - Access to your OS shell language
Adding a new language to your existing database is very easy. You can get the details from the Postgres CREATE LANGUAGE syntax docs. The way you will most often add a language is: CREATE LANGUAGE For example: CREATE LANGUAGE pltcl; The language must already be installed in the OS. You will use the dynamic libraries from the language to create and run your programs. You also need the Postgres component for your language. PL/pgSQL, PL/TCL and PL/Perl come with the standard distribution of Postgres. You can get other languages at http://pgfoundry.org/. Run a search on pgFoundry to see what they have.