Portability, was: using functions to generate custom error messages

Reshat,

> I certainly find it a must to use such features as referential
> integrity
> (even it costs a little), transaction support, custom objects, etc.
> which make postgreSQL stand apart from mySQL, etc. Such standard
> features as value checks, which I believe can be ported quite easily
> also don't arouse my doubts.
> I guess features that don't port well across DBs are stored
> procedures
> and consequently triggers. Those are the things I am referring to.
> For
> example, if we need to increment a field by a certain value once in
> every 2 weeks, would you suggest using stored procedures to
> accomplish
> it, or app logic? And in general, when would you say it's worth to
> use
> stored procedures?

As Joel points out, you are looking at "portability" from the
perspective of an interface programmer. Or, to explicate:

THE INTERFACE PROGRAMMER: "I put as much program logic as possible
into the interface and middleware code, limiting my database calls to
the most strict and limited set of SQL92. That way, my application is
PORTABLE to any SQL RDMBS."

THE DATABASE PROGRAMMER: "95% of my program's business logic,
including custom error messages and input validation, are in the
database and attached procedures. This makes my application design
PORTABLE to any front-end technology."

Both points of view are equally valid ... it just depends on who's
making the portability assessment. People tend to regard their area of
expertise as indispensable, and other technology as replacable.
Ideally, it should depend on the needs assessment of the application
being developed, but people are seldom that rational.

In a more direct answer to your question, Triggers and Procedures are
part of the SQL92/99 Specification, and are supported by all but one of
the major server database programs in their current version (those
being Postgres, Oracle, MS SQL Server, SAP DB, Phoenix, DB2, SyBase and
FrontBase, and the sole holdout being MySQL). However,
*implementation* of triggers and procedures varies widely between
platforms, as the spec leaves a lot of leeway here.

Thus, in the abstract, a design relying on triggers and procedures is
perfectly portable *in concept*, but the actual code would need to be
re-written for each target platform.

-Josh Berkus