Advice on Transfering functions from MS-MSSQL Server - Mailing list pgsql-general

Good Morning!

I've looked at the pgSQL docs but I'd like to get advice from someone thats
been "through the fire".

I'm attempting to move some code off of MS-SQL Server onto PostgreSQL.  Most
of my MS code is written in Microsofts T-SQL Functions.  I use a lot of
variables as well as Server-side cursors and temporary tables.    Does this
need to be done in C or pg tcl or PL/pgSQL or am I better doing this sort of
thing on the client side.  (thought I do need to protect the code as it is
proprietary)

Thanks in advance for any advice, below is a MS-SQL Function that is
simialar to the ones I need to transfer (with table & column names changed)

Darvin Zuch
mailto:darvin.zuch@autoprofile.com


CREATE PROCEDURE in_veBOXXERDecode @DINIV char(17), @DIROLOC int  AS
DECLARE @TDText varchar(30), @ENIGNEText varchar(30), @DOBText varchar(30),
@TransText varchar(30), @SRSText varchar(30), @ModelText varchar(30),
@TSERText varchar(30), @GBRAText varchar(30), @GVWRText varchar(30),
@Braketext varchar(30), @LEUFText varchar(30),  @HPText varchar(30),
@TypeText varchar(30), @MIRTText varchar(30), @DesignText  varchar(30),
@EARText char(4), @KAMText varchar(30),  @TDID int, @ENIGNEID int, @DOBID
int, @TransID int, @SRSID int,  @ModelID int, @TSERID int, @GBRAID int,
@GVWRID int, @BrakeID int, @LEUFID int,  @HPID int, @TypeID int, @MIRTID
int, @DesignID int, @EARID char(1), @KAMID int,  @nTDID int,@nENIGNEID
int,@nDOBID int,@nTransID int,@nSRSID int, @nModelID int,@nTSERID
int,@nGBRAID int,@nGVWRID int,@nBrakeID int,@nLEUFID int, @nHPID
int,@nTypeID int,@nMIRTID int,@nDesignID int, @TGDCount tinyint, @TGDQty
tinyint, @OriginID int

SELECT @TGDCount = 0
SELECT @KAMID=IM.KAMID, @KAMText=KAM.KAMText, @EARID=IM.EARID,
@EARText=EAR.EARText, @OriginID = Origin.OriginID
  FROM IM, KAM, EAR, Origin
  WHERE IM.IMValue = SUBSTRING(@DINIV,1,3) AND
    IM.EARID = SUBSTRING(@DINIV,10,1) AND
    KAM.KAMID = IM.KAMID AND
    EAR.EARID = IM.EARID AND
    Origin.HCSID = IM.HCSID AND
    Origin.OriginValue = SUBSTRING(@DINIV,11,1)

DECLARE in_vdDecodeBOXXER CURSOR FOR
SELECT VDS.TDID, VDS.ENIGNEID, VDS.DOBID, VDS.TransID, VDS.SRSID,
VDS.ModelID, VDS.TSERID, VDS.GBRAID, VDS.GVWRID, VDS.BrakeID, VDS.LEUFID,
VDS.HPID, VDS.TypeID, VDS.MIRTID, VDS.DesignID
  FROM VDS, TGD, PYTXTGD, HCS, IM
  WHERE VDS.HCSID = HCS.HCSID AND
    VDS.TGDID = TGD.TGDID AND
    VDS.VDSVALUE = SUBSTRING(@DINIV, TGD.START, TGD.LENGTH) and
    TGD.TGDID = PYTXTGD.TGDID AND
      PYTXTGD.PYTXID = HCS.PYTXID AND
    HCS.HCSID = IM.HCSID AND
    IM.IMVALUE = SUBSTRING(@DINIV, 1,3) AND
    IM.EARID = SUBSTRING(@DINIV, 10,1)
OPEN in_vdDecodeBOXXER
FETCH in_vdDecodeBOXXER INTO @TDID, @ENIGNEID, @DOBID, @TransID, @SRSID,
@ModelID, @TSERID, @GBRAID, @GVWRID, @BrakeID, @LEUFID, @HPID, @TypeID,
@MIRTID, @DesignID
WHILE @@FETCH_STATUS = 0
  BEGIN
  SELECT @TGDCount = @TGDCount + 1
  SELECT @nTDID=Coalesce(@TDID,@nTDID),
         @nENIGNEID=Coalesce(@ENIGNEID,@nENIGNEID),
         @nDOBID=Coalesce(@DOBID,@nDOBID),
         @nTransID=Coalesce(@TransID,@nTransID),
         @nSRSID=Coalesce(@SRSID,@nSRSID),
         @nModelID=Coalesce(@ModelID,@nModelID),
         @nTSERID=Coalesce(@TSERID,@nTSERID),
         @nGBRAID=Coalesce(@GBRAID,@nGBRAID),
         @nGVWRID=Coalesce(@GVWRID,@nGVWRID),
@nBrakeID=Coalesce(@BrakeID,@nBrakeID),
         @nLEUFID=Coalesce(@LEUFID,@nLEUFID),
         @nHPID=Coalesce(@HPID,@nHPID),
         @nTypeID=Coalesce(@TypeID,@nTypeID),
         @nMIRTID=Coalesce(@MIRTID,@nMIRTID),
         @nDesignID=Coalesce(@DesignID,@nDesignID)

  FETCH in_vdDecodeBOXXER INTO @TDID, @ENIGNEID, @DOBID, @TransID, @SRSID,
@ModelID, @TSERID, @GBRAID, @GVWRID, @BrakeID, @LEUFID, @HPID, @TypeID,
@MIRTID, @DesignID
  END
CLOSE in_vdDecodeBOXXER
DEALLOCATE in_vdDecodeBOXXER

IF EXISTS (SELECT DINIV FROM BOXXER WHERE DINIV = @DINIV)
  UPDATE BOXXER
    SET TDID = @nTDID, ENIGNEID = @nENIGNEID, DOBID = @nDOBID,
      TransID = @nTransID, SRSID = @nSRSID, ModelID = @nModelID,
          TSERID = @nTSERID, GBRAID = @nGBRAID, GVWRID = @nGVWRID,
      BrakeID = @nBrakeID, LEUFID = @nLEUFID, HPID = @nHPID, TypeID = @nTypeID,
          DesignID = @nDesignID, KAMID = @KAMID, EARID= @EARID, DIROLOC =
@DIROLOC,
      OriginID = @OriginID
    WHERE DINIV = @DINIV
ELSE
  INSERT INTO BOXXER
      ( DINIV, EARID, KAMID, ModelID, SRSID, TDID, ENIGNEID, DOBID, TransID,
TSERID, GBRAID, GVWRID, BrakeID, LEUFID, HPID, TypeID, DesignID, DIROLOC,
OriginID)
    VALUES
      (@DINIV,
@EARID,@KAMID,@nModelID,@nSRSID,@nTDID,@nENIGNEID,@nDOBID,@nTransID,@nTSERID
,@nGBRAID,@nGVWRID,@nBrakeID,@nLEUFID,@nHPID,@nTypeID,@nDesignID, @DIROLOC,
@OriginID)