Re: Why are stored procedures looked on so negatively?

On Sun, Aug 4, 2013 at 7:01 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

>
>
> I think part of the issue is that people tend to consider stored
> procedures part of the application's internal implementation where you
> just change all the call sites when you change the function.
>
> Normally stored proc are really more like a library API - something
> that's a bit of a pain to change due to asynchronous updates of apps and
> interface, multiple interface users, etc.
>

I think the above is just about exactly right. Also the fact is that since
this is communication across a network usually, asynchronous updates of
apps can be more or less a given.

>
> If you think about them that way the question "should this be done in
> apps or in a stored proc" must be asked for each individual procedure.
>

I would actually say it is worth stepping back from that and asking "what
do I want to get out of stored procedures anyway?" and building logic in
the application to make sure that happens.

For example, in LedgerSMB, we adopted a stored procedure-centric approach.
We decided to follow certain conventions in argument naming, and have the
application look up the arguments before the procedure call. Thus if the
function is redefined, the new version is used, and the API discovered at
call time.

Each approach has tradeoffs however. Our approach works great for what we
do with it, but it has some significant costs including the fact that this
approach is incompatible with function overloading since the name is the
discovery criteria.

If you have other needs, a different approach may be helpful. However it
really is imperative to sit down and look at the design questions.
--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml