Procedural language handlers exist in the core distribution for Perl, Python, Tcl, and PostgreSQL's own procedural language, PL/pgSQL.
Third party handlers exist, off the top of my head, for JavaScript (obviously; see TFA), R, Ruby, Scheme and shell. That list probably isn't remotely exhaustive.
To be honest, though, I've never really had much of a use for any language other than pl/pgsql and occasionally pl/r; I try to keep anything too complicated outside of the database.
The languages are, I guess, most useful for people who design their database as a datastore with an API, used by multiple independent clients (a common setup in enterprises). The API typically consists of stored procedures, and normal queries are disallowed for most usernames.
Being able to implement such an API in $DECENT_LANGUAGE and not pl/pgsql sounds like an enormous win.
Third party handlers exist, off the top of my head, for JavaScript (obviously; see TFA), R, Ruby, Scheme and shell. That list probably isn't remotely exhaustive.