Tom Lane wrote:
> Most people around this project think that the best way to do that is to
> push as much logic as you can into server-side stored procedures. That
> gives you every advantage that a persistent-prepared-statement feature
> would offer, and more besides:
>
It's simply to complicated to return recordsets through server-side
stored procedures. They are obviously designed to do complex data
manipulation, returning few output variables informing the caller about
final results. Returning records through sets of user-defined-types is
memory and performance waste (please see my previous post as reply to
Steve for more details). Plus it's hard to maintain and make
improvements to such a system. I hate to see 800 user types made for
every query we made as stored procedure.
I don't say it couldn't be done through sp but maybe you guys could
provide us with much easier (and efficient) way to organize and fetch
common SQL statements. Something between VIEWS and STORED PROCEDURES,
something like PARAMETERIZED VIEWS or PERSISTENT PREPARED statement.
Either way, it would be a place where we could use only PURE SQL syntax.
I think it's 90% of what any database application does.
> * you can push procedural logic, as well as SQL, out of the application
Application developing tools usually offer bigger set of functions,
objects, methods etc. than any DB stored procedure language can. There
is also debugging, code version control software, team development
software and lots of other stuff. It's just more efficient to keep the
logic in the application part of the system. Just compare the IDE
editors to any DB Admin Tool.
>> * you can improve performance by reducing the number of network round> trips needed to accomplish a
multi-SQL-statementtask>> regards, tom lane>
I couldn't agree more. Such tasks are decidedly for SP's. I'm thinking
about 90% of simple to mid-simple tasks (reports, logins, retriving
customer and item properties, warehouse inventory and other stuff) that
are simple matter of SQL or SQL's in a union with few parameters for
filtering the data.
I see programmers hard-coding such SQL statements in PHP, C++, Delphi or
VB projects. Why? Is it to complex to have it implemented in Postgre
engine? We have PREPARE statement, locally for the user. Is it possible
to take it globally, for all users, and not to forget it when all
connections dye?
It is a way to get all of SQL statement out of the application not only
"as much logic as you can". As a leader of our development team I find
it HIGHLY (and I mean HIGHLY) DESIRABLE.
Best regards,
Milan Oparnica