The other interesting thing about the PostgreSQL PL language architecture is that it is a fairly thin wrapper around these languages.
This means the kind of code you write in those languages is pretty much what you would write if you were doing general programming
in those languages minus some spi calls. Since the handler is a just a thin wrapper around the environment, the language environment must be installed on the database server before you can use the PL language handler. This means you can have these functions utilized in your SQL statements and you can write
in a language you feel comfortable with if you can get the darn PL compiled for your environment or someone has already kindly compiled it for your environment or that it is even compilable for your environment. The pluggable PL architecture means you can
write a PL Handler for your favorite language or invent your own language that you can run in the database. In the end
the barrier between code,data, and semantic constructs is more of a constraint imposed by compilers. If you have any doubts about the above statement, you need only look at some javascript injection attacks
to bring the statement home. One of my fantasies is
developing a language that morphs itself, that utilizes the database as its morphing engine and its OS and that breaks the illusion of data being data, code being code, and lacks rigid semantics.
Of the languages we have worked with, SmallTalk comes closest to a language that satisfies these ideals and Lisp to a much lesser extent. Lisp lacked the semantic elegance of SmallTalk among other things.

Most people are used to having their procedural language push their data around. PL code living in PostgreSQL allows your data to push your procedural code around in a set-based way. This is a simple but pretty powerful feature since data is in general more fluid than code. For interpretated/just-in time compiled languages it can live in the database, for compiled it has to call compiled functions.

Now I shall stop here and say there are consequences to a thin wrapper that are both good and bad.

Good/Bad - you are writing code you are used to. This is good because it makes people just getting used to relational database concepts feel at home.
This is bad because it gives one the false confidence
that the Romans will be happy when you impose your cultural bad habits on their perfect society. It is great to bring new ideas into the database, but try not to destroy the sanctity of the database by forgetting that you are in a database. Similar things have been said by DB programmers when SQL Server 2005 introduced .NET code in the database and you had all these reckless programmers doing
things in .NET code that would have been more efficient in Transact-SQL. Just because you can do it doesn't mean you should. More on that later.

Good - you can leverage all the goody libraries in your language of choice that others have written or you have written by calling the libraries from your database. With some caveats e.g. markings as safe and unsafe.

Bad - PostgreSQL is doing a context switch to push the code into the environment
your code is comfortable in. Generally the bigger and more complex the environment the more context switching that is happening.

Bad - In order to manipulate data, except for the built in languages SQL, PlPgSQL, and C,
PostgreSQL functions generally need to push the data into the language's environment and pull it out. This makes most languages somewhat suboptimal
for set returning functions or functions that consume a lot of data.

As we mentioned in a prior article Trojan SQL Function Hack - A PL Lemma in Disguise
not all languages are created equal as far as PostgreSQL is concerned and PostgreSQL has its favorites. Just as annoying as the MySQL storage engine idiosyncracies where you can have foreign keys in one storage engine and they are ignored in another,
similar can be said with PL languages in PostgreSQL - they all handle sets differently and set returning functions are easier to write in some PL's than in others. Not to mention each programming environment
has certain idiosyncracies of its own which make this useful yet still Leaky abstraction apparent.

Even if PostgreSQL did not have its
favorites, one must keep in mind that languages are designed for a particular reason. They are designed to satisfy a particular language designers philosophies and goals. This means that Perl is optimized for the certain kinds
of problems that Larry Wall liked to solve (e.g. string manipulation) and to solve them the way that Larry thought was fitting. Similarly R, S, S-Plus were designed for scientific, statistical processing, graphing.
R takes some effort to get used to its terminology of data frames and factors and its way of pushing data into arrays and defining functions, but its well-worth it for what it does. Generally speaking the PL languages are not optimized for pushing data in a SET-oriented way and if you try to use
them for something they were not really designed well for, you may feel comfortable but your database will suffer for your illusion of comfort. This false comfort
leads people to write otherwise simple SET code in PlPython when it could have been done more efficiently and simply in PostgreSQL SQL function language or PLPgSQL language. Some people
further like to encapsulate things
in functions that shouldn't be encapsulated in functions in the first place because
it gives them a false sense of comfort to shove stuff that they couldn't figure out how to write in a set-based way into a loop-di-loop blackbox. It may be amusing to write needlessly complicated code and kill cockroaches with hammers, but it is not a terribly efficient way of occupying your time. In fact
the most impressive programmers are just clear thinkers. The real geniuses in programming are those who can restate an unsolvable problem into a solvable one or don't get caught up in the mob thinking that causes groups of people to simultaneously come up with the wonderful idea of solving the same wrong problem.

General rule of thumb when deciding which language to program a particular functionality

Can you do the same thing in plain old SQL language? If you can you should. SQL functions are generally inlined which makes them more efficient from a planner perspective

Most people don't feel comfortable writing in C - so you may want to throw this out of the equation although it is close in priviledge to SQL functions and for intense processor functions
more efficient

Does the function require some intensive calculation or functionality that doesn't exist in PostgreSQL or that is just faster to write and process in PLPerl, PL/R or that these languages have built-in already etc.? Here you probably want to do some benchmarks to be sure to make sure the
lose in context switching is less than the gain in efficiency.

Will you have to run this database in an environment that doesn't support your poison of choice?

Do you feel comfortable in X language and does it look like something you can feel comfortable with.

Below are links to various articles that demonstrate some uses of PLs :

Hubs of Choosing the right Database Procedural <b>Language</b> PL
hubs about Java Languages to ... and the domain specific languages PL/R, PL/SH and gaining popularity Skype released PL/Proxy. There are others in the family such as PL/Tcl, PL/PHP, PL/Ruby, PL/Schema (a dialect of Lisp), PL/Java, PL/Lua and PL/LOLCode (for kicks ...

Isn't it PL/Scheme, not PL/Schema? Regarding your comment about Smalltalk vs Lisp when looking for a language that "morphs itself" (and saying that Lisp is less fitting), mistaking "schema" for "scheme" makes it sound like you simply have more experience with Smalltalk and are less familiar with Lisp. It may not be true, but it could be construed that way. Please proofread to avoid confusion.

I'm used to thinking of schemas when talking about databases so that was a bit of a Freudian slip. Regarding Lisp - wasn't trying to imply it can't morph itself. Mostly I just don't care much for the syntax of Lisp and that was my big gripe with it. You are right though that I have more experience with SmallTalk than Lisp and haven't programmed in Scheme since college.

Also, regarding point 4, above, specifically "[pushing data between PostgreSQL and some language interpreter] makes most languages somewhat suboptimal for set returning functions or functions that consume a lot of data." I'm not sure I buy this. Sure, converting data into a format the language backend likes takes time and cycles, but less time and cycles than it would to push all that data into some front-end application, process it there, and push it back. One of the huge advantages of a PL is that you can keep database-like functions (data integrity, for instance, or massive data crunching) in the database, where the data don't have to move too far between storage and processing.

couldn't agree with you more. Regarding the note about set returning functions being sub optimal. for the most part you are right its probably more efficient to have the function in the db than call the function from native code. Our comment was more along the lines of writing set returning functions in something like SQL/PLPGSQL that in general writing set returning functions in those built in languages is more efficient than writing in say PLPython or PL/R. Of course some things you just can't do easily in SQL/PLPGSQL so then you would resort to another language and efficiency gains may actually be higher when you consider other things like processing speed in that language and just more efficient functions you can piggy back on.

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.Enter the string from the spam-prevention image above: