On Tue, Jul 23, 2013 at 5:29 PM, Some Developer <someukdeveloper@xxxxxxxxx> wrote: > I've done quite a bit of reading on stored procedures recently and the > consensus seems to be that you shouldn't use them unless you really must. I think that mostly speaks to the method you used for finding things to read. This is a well known holy war. > > I don't understand this argument. That is a conclusion, not an argument. You didn't give us the argument behind the conclusion! > If you implement all of your logic in the > application then you need to make a network request to the database server, > return the required data from the database to the app server, do the > processing and then return the results. A stored procedure is going to be a > lot faster than that even if you just take away network latency / transfer > time. Sorry, I don't get this at all. Whether you use a stored procedure or not, the database needs to be told what to do by the outside word, and needs to return the result to the outside world. So you can not get rid of that minimal round trip, no matter what, unless your database becomes solipsist. Now, if the application-side code needs to make a lot of round trips to the database in order to implement one logical unit of work, that is a different matter and stored procedures could help there (but so could consolidating the round trips into a fewer number of more sophisticated SQL--which is often but not always possible). One of the very annoying uses of stored procedures I see is insisting that all access goes through them, with no direct access to the underlying tables via ordinary SQL. They have now replaced one of the most successful, powerful, and well-known data access APIs ever, with some home grown API that is probably half-baked. Sometimes a case can be made for that (particularly for large bureaucratic organizations, or intensely regulated ones) , but don't expect it to be free of consequences. > I'm in the middle of building a database and was going to make extensive use > of stored procedures and trigger functions because it makes more sense for > the actions to happen at the database layer rather than in the app layer. > > Should I use them or not? Are you a one man shop, and always will be? If so, i think it is mostly a matter of what you prefer developing in, and what you are most used to developing in. If you hire someone to help you out, do you want that person to be able to do interesting (and perhaps bad) things with the database through SQL, or do you want them to be mostly restricted to changing the font and color of the web page showing the results? A case could be made for either way. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
