Re: Is it considered good practice to use stored procedures for most tasks?

Is it considered good practice to use stored procedures for most tasks?
My experience is that if you keep the business logic out of the database,
but for instance deterministic data transformations, data integrity
functions etc. in - a view is a good example of it, but it can be a stored
procedure, trigger, converting results into JSON or whatever you like -,
you can save much work on the back end code because you deliver solid data
with fix shape. It acts like a good API to your data without involving the
problem of changing business features.
In the database you have an *actual implementation* of a *data model*. In
my opinion the *data model* is more business related or how the software
developers/architects imagine these entities. The implementation is more
how you *actually build it* in the database - your table structure, level
of normalization or denormalization, handling defaults or implementing
triggers for better consistency etc. The *data model* can be relative
stable - as probably a number of other system components are depending on
it and it represents the understanding of the entities related to the
business.
On the other hand your *actual implementation* may change due to table or
query optimizations, for instance. Such DB refactoring can be very painful
if the DB internals are exposed to the back end (experience). Plus
generally speaking most back end developers are not really good optimizing
databases and understanding the performance implications what a database
can offer and twisting their mindset around SQL which is a declarative
language, not a procedural.
If you bring business logic into the database - over the time you can
easily go into trouble. The decision is not always easy but in those case
go on the safe side: if you can't decide if it is business logic or else
then maybe better to consider it as business logic first.

BR,
salmonix

On Thu, Apr 18, 2019 at 10:36 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> Gaetano Mendola wrote:
> > For sure do not allow your application to touch directly tables, use
> views and sp,
> > lately I'm abandoning views for table functions.
>
> As far as I know, a table function result is materialized on the server,
> and you have to wait for the query to finish before you can retrieve the
> first row.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>