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

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
senderby 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
managementof the company is strictly prohibited." 

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?

>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?

The SQLgenerator software was written as a Windows program for MS SQLserver
only and my company purchased the software. I was trying to find if there
is some similar utility for Postgresql that would read the table
structures, write the CRUD / DML functions, and then add the functions to
the database. That way all I would have to do is customize the functions
after they were written.

The data I am porting is from very old software, pre 1995 DOS format in
xbase tables and flat files. We are writing small scripts in whatever works
to push it into postgresql. There is no Java layer.


I have found an example of what a postgresql function would look like that
does an insert. Until today I had not been able to find insert function
examples. This site has a create / update function:
http://stackoverflow.com/questions/2102613/postgresql-insert-that-depends-on-data-in-another-table-best-practice


This site has a script that says it will CRUD generate functions but I just
found it today and haven't tried it yet, it is tested for postgresql
versions 8.1.9 and 7.4.18 and seems to be what I was looking for.
http://myleshenderson.com/index.php/2007/11/16/postgresql-insert-function-generator


The introduction to this script reads:
"PostgreSQL Insert Function Generator.  I like the plpgsql procedural
language for PostgreSQL more than it is appropriate to like a procedural
language. I find the reward:effort ratio to be quite high. Since one can do
so many cool things with it, why waste time writing boring insert functions
for tables? Here’s my plpgsql function which generates the create function
statements for tables in a database."

I don't see a reference to this site in the maillist archives and wonder if
anyone has used it or has found something similar posted for 9.1?

Thanks, Margaret"This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may
containcompany proprietary, privileged or confidential information.   If you are not the intended recipient(s), please
contactthe 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
seniormanagement of the company is strictly prohibited."