Lisp as PostgreSQL trigger / stored procedure language

Travis Cross <travis-ZKNsLcePPwMu8xhjR5IN5AC/[email protected]>
Newsgroups gmane.lisp.clsql.devel
Organization CrossWire
Message-ID <[email protected]>
After years of loyalty to MySQL, I have finally jumped ship and landed 
squarely in the PostgreSQL camp.  Now I'm in love with the ability to 
craft database-side functions, rules, and triggers.  The flexibility to 
write functions and triggers in PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python 
has completely changed the way I approach an application.

There's just one problem.  More and more of my application logic is 
being crafted in languages other than Lisp!  The cause is that there 
doesn't seem to be a call handler that would allow Lisp to run inside 
the PostgreSQL server, and I can't determine if or how a compiled Lisp 
function might be able to be used in place of a compiled C function.

I am considering writing the needed call handler (PL/CL, PL/CLu), but 
I'm kind of a hack when it comes to producing substantial amounts of C 
code, especially in unfamiliar territory.  (The PostgreSQL documentation 
gives a short example call handler and notes, "Only a few thousand lines 
of code have to be added instead of the dots." ;) )

Since Lisp is a compiled language (I generally use SBCL), it seems to me 
that there should be some way to call a compiled Lisp binary in a 
somewhat analogous fashion to calling a C binary.  Does anyone have 
insights here on how to make this work?

Has anyone else looked into this or taken an interest?  Is there 
anything that's fallen below my radar screen?

Insights? Suggestions?  Comments welcome.

As Lisp is generally well regarded and received as a powerful extension 
language, it would seem to me that Lisp integration in PostgreSQL could 
do well to boost the profile of both CL and PostgreSQL.

Thanks,

-- Travis

Sidenote for those unfamiliar with PostgreSQL:
PostgreSQL doesn't naturally support any languages for functions or 
triggers per se (except compiled C).  Instead, PostgreSQL provides an 
interface for procedural language handlers.  The call handler receives a 
pointer containing argument information and OID of the function to call, 
and the handler is expected to return a result (Datum).  There are also 
two classes of embedded languages in PostgreSQL: trusted and untrusted, 
which indicate if the language can cause side-effects outside of the 
database (such as sending mail).
http://www.postgresql.com/docs/7.4/static/plhandler.html
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.