Re: Reason(s) not to use a stored procedure

In addition to all of these good points, there is also an issue of scalability. When the application
logic sits in the database, it limits your options for load balancing across potentially large
numbers of database servers and application servers. In a typical enterprise environment there
are all kinds of legacy datastores that all have to be accessible, and you really don't want to
port your code base individually to all of them. Who was it who said that computer science is
the business where we solve every problem with one more layer of indirection?

If you're in that sort of environment, those considerations tend to reduce overall complexity;
if you're not, then they tend to increase it.

----- Original Message ----
From: Ilan Volow <listboy(at)clarux(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Sent: Tuesday, January 22, 2008 9:19:18 AM
Subject: Re: [NOVICE] Reason(s) not to use a stored procedure

IMHO, like most everything dealing with any kind of software development, it's an engineering tradeoff.

When I write plpgsql stored procedures, I am trading:

- Easy portability between databases (to the extent that SQL is portable between databases)

- Niceties like IDE's with useful IDE stuff like being able to search through code (I had to write a custom application to do this with plpgsql).

- Ease of SCM (I am having to write a custom application to do this with plpgsql)

- Ease of debugging (via using a real debugger, that I have to do absolutely no work at all to install and that I don't have to buy)

- Maintainability. I personally find maintaining large bunches of plpgsql to be a major PITA. Code can easily get lost if you restore a dump in the wrong way.

- A good, clean separation between application layers and storage layers

for:

- Using loops and variables so I don't have to write and debug a cluster**** of SQL spaghetti.

- Avoiding getting involved with introducing a separate middle-ware server.

- Not having to change the Objective-C code for my rich-client, recompile it, and redeploy it to get the change in behavior I want. Updates are instantaneous and centralized.

Probably not the stuff that your professor mentioned, but it's what's been my experience. Other experiences may vary.

On Jan 22, 2008, at 8:49 AM, Mag Gam wrote:

I was in an academic meeting where a professor was preaching the reasons why not to use a stored procedure. He advised to always use SQL instead of a procedural language. Can someone please shed some light on this?

THANKS

Ilan Volow
"Implicit code is inherently evil, and here's the reason why:"

____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ