CRUD functions, similar to SQL stored procedurs, for postgresql tables?

[MargaretGillon@xxxxxxxxxxxxxx]

We use some SQLserver databases that have stored procedures for all
C.R.U.D. functions so the same code is used no matter what language the
developer is working in. The procedures are built by a master package that
reads the table structures and creates the CRUD procedures. Then we modify
the CRUD procedures for special issues on each table. This has worked well
for us because we don't lose the logic if we have to change languages and
we can return specific information on update failures to the calling object
that helps debugging. Right now most of what we are doing is loading data
from many old systems / many old languages (some not OOP) into a new
Postgresql database. We are not sure yet what language or platform the
replacement software will be written in so it is too early to set up MVC.

I would like to do something similar with Postgresql functions. Are there
any examples or best practices for this?

Thanks,
Margaret

---------------------------------------------------------------------------
Examples:

-----------------------------------------------------------------------------
 Insert a single record into datalink
----------------------------------------------------------------------------

CREATE PROC CHRM_datalink_Insert
         @dlID1 uniqueidentifier,
         @dlID2 uniqueidentifier,
         @dlLTID uniqueidentifier,
         @dlActive char(1),
         @dlEditBy uniqueidentifier = NULL,
         @dlEditDate datetime = NULL,
         @dlID uniqueidentifier = NULL
AS

INSERT datalink(dlID1, dlID2, dlLTID, dlActive, dlEditBy, dlEditDate, dlID)
VALUES (@dlID1, @dlID2, @dlLTID, @dlActive, @dlEditBy, COALESCE
(@dlEditDate, getdate()), newid())


GO

--------------------------------------------------------------------------
-- Delete a single record from datalink
----------------------------------------------------------------------------

CREATE PROC CHRM_datalink_Delete
         @dlID1 uniqueidentifier,
         @dlID2 uniqueidentifier,
         @dlLTID uniqueidentifier
AS

DELETE        	datalink
WHERE         	dlID1 = @dlID1
 AND         	dlID2 = @dlID2
 AND         	dlLTID = @dlLTID


GO

-----------------------------------------------------------------------------
 Update a single record in datalink
----------------------------------------------------------------------------

CREATE PROC CHRM_datalink_Update
         @dlID1 uniqueidentifier,
         @dlID2 uniqueidentifier,
         @dlLTID uniqueidentifier,
         @dlActive char(1),
         @dlEditBy uniqueidentifier = NULL,
         @dlEditDate datetime,
         @dlID uniqueidentifier
AS

UPDATE        	datalink
SET        	dlActive = @dlActive,
         dlEditBy = @dlEditBy,
         dlEditDate = COALESCE(@dlEditDate, getdate()),
         dlID = COALESCE(@dlID, newid())
WHERE         	dlID1 = @dlID1
 AND         	dlID2 = @dlID2
 AND         	dlLTID = @dlLTID

GO

"This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain company proprietary, privileged or confidential information.   If you are not the intended recipient(s), please contact the sender by reply e-mail, advise them of the error and destroy this message and its attachments as well as any copies. The review, use or distribution of this message or its content by anyone other than the intended recipient or senior management of the company is strictly prohibited."

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general