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

MargaretGillon(at)chromalloy(dot)com 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?