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:[EMAIL PROTECTED]
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)
