MargaretGillon@xxxxxxxxxxxxxx wrote:
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
I see nothing tricky in your CRUDdy procedures. I would think porting
the sql-generator would be pretty straight forward. "Except for the
names and a few other changes, the story's the same one.": read the
system catalogues and generate your procs-cum-functions. Your jdbc
interactions should turn out largely unchanged if the names of the
routines are directly transferable.
Has this approach failed? Or have I mis-understood?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
