We've been doing something kinda similar in LedgerSMB with versions of PostgreSQL going back to 8.1 when it was current....
Our approach was different though. We have object methods explicitly mapped to stored procedures by name (this is done to limit the stored procedures exposed by the application). If the number of arguments changes, the middleware does not need to be updated.
The queries are then called through an interface that, in essence, hands a hashref (in Perl) and a stored procedure name to a mapper function which then looks up the argument names and assembles the query.
This is then called, with an optional order by clause, and the results returned as a list of hashrefs.
While this isn't the exact technologies listed here, the approach is something I have found wonderful and so it's probably worth sharing.
It this part of the LedgerSMB project? If so, what part of the source code handles things? Would be interesting to see how you have implemented it.
When you create a new function, do you have to do anything in addition to do CREATE FUNCTION, before you can use the function in your front-end/backoffice/whatever external system component?
The primary magic happens between lines 112 and 170 which is where we look up the argument names and process them. Then this gets handed off to LedgerSMB.pm's "call_procedure" interface which builds the query and executes it.
It isn't great code and there is room for improvement. It will be improved over coming versions. The 1.4 codebase is moving to use Moose for newer code (all code by 2.0), and the combination of Moose and this approach to method mapping is proving quite useful.
I am also working on a more ambitious library for doing this query sort of thing which will add the possibility to add windows and windowed columns onto stored procedure calls at mapping time, so you can ask for a GL running balance while specifying ordering.... That probably won't be ready for 1.4 though.
I looked at the code. Reminds a bit of what we are currently doing. We also have a Perl function for each SP. But that's what I want to get away from, the middle-layer should be fully capable of automatically mapping the request to the appropriate SP, like in this proof-of-concept. Can you think of one scenario when it isn't possible to do so? I've tried but haven't came up with anything.
Also, in execute_method, what if two functions matches the same name? Looks like you just pick the first one it finds. Shouldn't you throw an error if unsure which one to pick?
Since the name is the discovery criteria, function overloading is not supported in this interface. It's possible to define a different one, but the idea here is that the stored procedures should be discoverable based on name. So we have test cases that raise errors when functions are overloaded that we don't know should be.
So in answer to your last question, behavior is undefined, and such a condition will trigger failures in our database unit tests.
On the other side, the reason to enforce an explicit mapping is because it isn't clear how much you really want to trust the client to execute any function in the database at all. An earlier version used AUTOLOAD to map these methods in directly so no middleware changes would be necessary. We abandoned that approach in part because of concerns it could be abused. So the issue isn't what can you do with my framework that you can't do with your framework but actually the other way around. Do I want to whitelist or blacklist stored procedures? Whitelisting seems safer.
A future version will probably use Module::Compile to build mappings from the database, but we haven't defined interfaces sufficiently to make this happen. However one idea is to tie Perl classes to PostgreSQL complex data types, and grab functions which return those complex types. Since all procedures are supposed to return useful data (even delete operations might return the deleted row), then this might work.
Again that's in the design phase.
Edit: It occurs to me that if the return type and the function name become the discovery criteria, then two functions could have the same name but different args as long as they have different return types.
Our approach was different though. We have object methods explicitly mapped to stored procedures by name (this is done to limit the stored procedures exposed by the application). If the number of arguments changes, the middleware does not need to be updated.
The queries are then called through an interface that, in essence, hands a hashref (in Perl) and a stored procedure name to a mapper function which then looks up the argument names and assembles the query.
This is then called, with an optional order by clause, and the results returned as a list of hashrefs.
While this isn't the exact technologies listed here, the approach is something I have found wonderful and so it's probably worth sharing.