Re: CRUD functions, similar to SQL stored procedurs, for postgresql tables? - Mailing list pgsql-general

MargaretGillon@chromalloy.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?