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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
