Thread: stored procedures

Hi,

I am very new to PgSQL and I am just trying to figure out what for to
use stored procedures.
What I read/tested up to now is that pl/pgsql seems to be a bit
clumsy to write. Possibly needing a lot of
trial & error.

So, I am not really sure what is the benefit of writing logic inside the DB.
Is there a performance benefit compared to processing via PHP?

I do not really see the use for me to separate logic from the other logic,
'cause then you'll have to worry about 2 sources!
(I usually have a DB only used by that specific application, and if
not it is at least the same language, so global function are stored
in shared code libraries.)

I see this is a powerful feature and it seems to make sense, but when
I scan across my apps, currently using MySQL, I am having a hard time
to imagine where I could use it.
As far as I figured now, the only thing I would put into DB-Logic
would be stuff like "deleting or updating related tables". But this
is what REFERENCES .... CASCADING does as well!

It would help me lot, if anyone could help me here to jump on that train.
Maybe one can tell me what he does with this opporunity.

Thank you very much in advance,

Alex
--
___________________________
Human Touch Medienproduktion GmbH
Am See 1
17440 Klein Jasedow

Alexander Lohse
Tel: (038374) 75211
Fax: (038374) 75223
eMail: al@humantouch.de
http://www.humantouch.de

> So, I am not really sure what is the benefit of writing logic inside the
DB.
> Is there a performance benefit compared to processing via PHP?

    One key benefit aside from anything else would be the ability to call
the stored proceedure no matter how you were interfacing to the database.
I've found this useful on many occasions.

> I do not really see the use for me to separate logic from the other logic,
> 'cause then you'll have to worry about 2 sources!

    Sort of, it really depends on exactly what you're doing. Doing things in
the database might not be the best thing for you..

> (I usually have a DB only used by that specific application, and if
> not it is at least the same language, so global function are stored
> in shared code libraries.)

    What if you write a web app and a windows app that connects to the same
database? Storing logic in the database allows both to share the code... One
of several uses, of course.

> I see this is a powerful feature and it seems to make sense, but when
> I scan across my apps, currently using MySQL, I am having a hard time
> to imagine where I could use it.

    It might not benefit you at all -- no one says you have to use it.. :-)

> It would help me lot, if anyone could help me here to jump on that train.
> Maybe one can tell me what he does with this opporunity.

    Hope I helped, I'm sure others have more (and probably better) examples
for you..

-Mitch