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