Re: Procedural Languages

On Thu, May 31, 2012 at 7:36 AM, John Townsend
<jtownsend(at)advancedformulas(dot)com> wrote:
> There are least 10 Procedural Languages available for PostGreSQL. The one
> that comes with the installation is PL/pgSQL.
>
> Which ones do you use and why?

Virtually all the time I use PL/PGSQL. The reason is that I think
that the primary purpose of a stored procedure language is to
encapsulate database functionality inside the database. Secondarily I
write functions in plain old SQL. With SQL becoming more
full-featured (CTE's etc) the use cases I have for PL/PGSQL are
actually shrinking. The major reasons I use PL/PGSQL as opposed to
SQL are actually shrinking. The major reasons I use it today are:

1) Exception handling and triggers
2) There are a few cases where logic is sufficiently complex that the
procedural extensions are really helpful.
3) Backwards-compatibility with older PostgreSQL versions (won't use
writeable CTE's for a while)
4) named input arguments, so if there are more than a few arguments,
I will use PL/PGSQL just because I think it leads to more readable
code.

My view is that PL/PGSQL rocks. Code written in PL/PGSQL is clear,
readable, and database-friendly. While there may be niches for other
languages but for db stuff, it is my workhorse.

BTW, I second the point about listen/notify. I have some sample code
there I can share. It's not perfect by any means and would probably
cause annoyances if used as is in production but you can find it at
https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/branches/1.3/utils/notify_short/

Best Wishes,
Chris Travers