As the title says, I am looking for a library that can connect to Progres and execute a stored procedure with either input or output parameters. Or even a user defined function.

I’ve checked the documentation of these three libraries:

  • cl-progres
  • pg-dot-lisp
  • clsql

but there seems to be no mention or example of executing a stored procedure. Is it just not documented? Are there other ways I can do it apart from these three?

  • defaultxr@alien.topBEnglish
    1·
    10 months ago

    I use Postmodern for Postgres and I quite like it. Never used stored procedures but the readme seems to suggest that it does support them:

    • Convenient support for prepared statements and stored procedures
  • ManWhoTwistsAndTurns@alien.topBEnglish
    1·
    10 months ago

    Why not just store data and have a procedure which executes with that data as an argument? That’s completely equivalent to what you’re asking for. I don’t imagine there are any libraries that support storing lambda structures in postgres or any other database, because it doesn’t make a lot of sense to do so (lambdas could require arbitrary amounts of closure data to be saved, and are very implementation dependent).

    It’s certainly possible, but you should probably be rethinking exactly what you want to do; one thing you might end up doing is writing a sort of store-procedure function which stores the data necessary for a retrieve-procedure function to create the lambda on demand.

    Maybe I’m misunderstanding what you mean by stored procedure and you just want to call a postgres procedure defined with ‘create procedure…’ . I think you could do that with postmodern and simply the name of the procedure as a keyword, i.e.

    > (sql (:my-proc 1 2))
"my_proc(1, 2)"
    • AxenZh@alien.topOPBEnglish
      1·
      10 months ago

      Thanks. I’ve come across a page describing how to execute stored procedure in CLSQL:

      if you want just execute stored procedure then (execute-command "sp_bla 1, 2")
      If your stored procedure returns table or single value then (query "sp_bla 1, 2")

      which is quite similar to Lispworks apart from wrapping the call to the procedure with PL/SQL BEGIN END pair.
      (sql:execute-command "BEGIN my_procedure(1, 'foo'); END;")