Re: [GENERAL] stored procedure revisited

Kane Tao wrote:
>
> OK. Stored procedures are one of the most powerful tools available in
> database design and implementation. U guys/gals forget that placing
> business rules in stored procedures allows for flexibility and modular
> reusability of code as well as speeds up such commonly used queries.
> Although I have never had the need to use stored procedures (never had a
> project that used PostgreSQL that was large enuff), PostgreSQL allows u to
> create SQL functions...which I believe meet the criteria u need for stored
> procedures?
>

Actually, the CREATE FUNCTION is insufficient as a substitute for SP,
although it certainly can be improved upon. For example, functions
cannot return more than one column :)

> I could be wrong, but I dont know of any database that allows you to run an
> SQL stmt without having the engine process the entire query in the
> background. Even those that return limited sets must execute a sort on the
> entire dataset before a subset can be returned...
>

I don't know how SP is implemented since none of the commercial RDBMS
publishes their sources, but they've all claimed that SP saves parsing
time and saves query plan time (it's generated once and stored). Need
some database experts to verify this point :) And like you said, it's
one of the most powerful tools available for database implementation. I
want the ability simply because of its conceptual abstraction, even if
w/out any of the performance benefit.

Come on, everybody, speak out your thought on this matter :)

Regards,

yin-so chen