Re:Portando Procedimiento de Oracle a Postgresql

Si ya sabía que no se usan commit y rollback... pero he tratado de solucionar el problemas para reemplazar las funcionalidades del procedimiento pero siempre me manda a la exception diciendo que la transaction es de sólo lectura. Y al hacerle otras modificaciones me dice que se retorna datos cuando no se esperaba nada como retorno

el procedimiento en pl/pgsql me quedó de la siguiente manera que es la primera transformación que he hecho.

CREATE OR REPLACE function M_Production_Run
(
PInstance_ID NUMERic
)
returns void
AS $$
declare
-- Logistice
ResultStr VARCHAR(2000);
Message VARCHAR(2000);
Record_ID NUMERic;
p record;
pp record;
pl record;
bom record;
-- Parameter
Cur_Parameter CURSOR (PInstance NUMERic) IS
SELECT i.Record_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date
FROM AD_PInstance i, AD_PInstance_Para p
WHERE i.AD_PInstance_ID=PInstance
AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
ORDER BY p.SeqNo;
-- Parameter Variables
MustBeStocked CHAR(1);
IsCreated CHAR(1);
Processed CHAR(1);
Client_ID NUMERic;
Org_ID NUMERic;
--
Line NUMERic;
NextNo NUMERic;
CountNo NUMERic;
-- ProductionPlan
/* CUR_PP CURSOR IS
SELECT *
FROM M_ProductionPlan
WHERE M_Production_ID=Record_ID
ORDER BY Line, M_Product_ID;*/
-- BOM Lines
CUR_BOM CURSOR (Product_ID NUMERic) IS
SELECT *
FROM M_Product_BOM
WHERE M_Product_ID=Product_ID
ORDER BY Line;
-- ProductionLines which are non-stocked BOMs (need to be resolved)
CUR_PLineBOM CURSOR (ProductionPlan_ID NUMERic) IS
SELECT pl.M_ProductionLine_ID, pl.Line, pl.M_Product_ID, pl.MovementQty
FROM M_ProductionLine pl, M_Product p
WHERE pl.M_ProductionPlan_ID=ProductionPlan_ID
AND pl.M_Product_ID=p.M_Product_ID
AND pl.Line<>100 -- Origin Line
AND p.IsBOM='Y' AND p.IsStocked='N';
-- Posting
/*CUR_PL_Post CURSOR IS
SELECT pl.M_ProductionLine_ID, pl.AD_Client_ID, pl.AD_Org_ID, p.MovementDate,
pl.M_Product_ID, pl.M_AttributeSetInstance_ID, pl.MovementQty, pl.M_Locator_ID
FROM M_Production p, M_ProductionLine pl, M_ProductionPlan pp
WHERE p.M_Production_ID=pp.M_Production_ID
AND pp.M_ProductionPlan_ID=pl.M_ProductionPlan_ID
AND pp.M_Production_ID=Record_ID
ORDER BY pp.Line, pl.Line;*/

BEGIN
-- Update AD_PInstance
DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID);
ResultStr := 'PInstanceNotFound';
UPDATE AD_PInstance
SET Created = CURRENT_DATE,
IsProcessing = 'Y'
WHERE AD_PInstance_ID=PInstance_ID;
RAISE NOTICE 'Primer Update';
-- Get Parameters
ResultStr := 'ReadingParameters';
FOR p IN Cur_Parameter (PInstance_ID) LOOP
Record_ID := p.Record_ID;
IF (p.ParameterName = 'MustBeStocked') THEN
MustBeStocked := p.P_String;
DBMS_OUTPUT.PUT_LINE(' MustBeStocked=' || MustBeStocked);
ELSE
DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
END IF;
END LOOP; -- Get Parameter
DBMS_OUTPUT.PUT_LINE(' Record_ID=' || Record_ID);
-- Processing??? Lock ????
-- TODO
/**
* Get Info + Lock
*/
ResultStr := 'ReadingRecord';
SELECT IsCreated, Processed, AD_Client_ID, AD_Org_ID
INTO IsCreated, Processed, Client_ID, Org_ID
FROM M_Production
WHERE M_Production_ID=Record_ID
FOR UPDATE;
/**
* No Action
*/
IF (Processed <> 'N') THEN
Message := '@AlreadyPosted@';
GOTO FINISH_PROCESS;
END IF;

/**************************************************************************
* Create Lines
*/
IF (IsCreated <> 'Y') THEN
-- For every Production Plan
FOR pp IN SELECT *
FROM M_ProductionPlan
WHERE M_Production_ID=Record_ID
ORDER BY Line, M_Product_ID LOOP
-- Delete prior lines
DELETE M_ProductionLine
WHERE M_ProductionPlan_ID=pp.M_ProductionPlan_ID;
-- DBMS_OUTPUT.PUT_LINE('ProductionPlan=' || pp.M_ProductionPlan_ID);
-- Create BOM Line
ResultStr := 'CreatingLine BOM';
Line := 100; -- OriginLine
AD_Sequence_Next('M_ProductionLine', pp.AD_Client_ID, NextNo);
INSERT INTO M_ProductionLine
(M_ProductionLine_ID, M_ProductionPlan_ID, Line,
AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
M_Product_ID, MovementQty, M_Locator_ID, Description)
VALUES
(NextNo, pp.M_ProductionPlan_ID, Line,
pp.AD_Client_ID,pp.AD_Org_ID,'Y',CURRENT_DATE,0,CURRENT_DATE,0,
pp.M_Product_ID, pp.ProductionQty, pp.M_Locator_ID, pp.Description);
-- Create First Level
FOR bom IN CUR_BOM (pp.M_Product_ID) LOOP
ResultStr := 'CreatingLine Products';
Line := Line + 100;
AD_Sequence_Next('M_ProductionLine', pp.AD_Client_ID, NextNo);
INSERT INTO M_ProductionLine
(M_ProductionLine_ID, M_ProductionPlan_ID, Line,
AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
M_Product_ID, MovementQty, M_Locator_ID)
VALUES
(NextNo, pp.M_ProductionPlan_ID, Line,
pp.AD_Client_ID,pp.AD_Org_ID,'Y',CURRENT_DATE,0,CURRENT_DATE,0,
bom.M_ProductBOM_ID, -pp.ProductionQty*bom.BOMQty, pp.M_Locator_ID);
END LOOP;
-- While we have BOMs
LOOP
-- Are there non-stored BOMs to list details?
ResultStr := 'CreatingLine CheckBOM';
SELECT COUNT(*) INTO CountNo
FROM M_ProductionLine pl, M_Product p
WHERE pl.M_Product_ID=p.M_Product_ID
AND pl.M_ProductionPlan_ID=pp.M_ProductionPlan_ID
AND pl.Line<>100 -- Origin Line
AND p.IsBOM='Y' AND p.IsStocked='N';
-- Nothing to do
EXIT WHEN (CountNo = 0);
--
-- Resolve BOMs in ProductLine which are not stocked
FOR pl IN CUR_PLineBOM (pp.M_ProductionPlan_ID) LOOP
ResultStr := 'CreatingLineBOM Resolution';
Line := pl.Line;
-- Resolve BOM Line in product line
FOR bom IN CUR_BOM (pl.M_Product_ID) LOOP
ResultStr := 'CreatingLine Products2';
Line := Line + 10;
AD_Sequence_Next('M_ProductionLine', pp.AD_Client_ID, NextNo);
INSERT INTO M_ProductionLine
(M_ProductionLine_ID, M_ProductionPlan_ID, Line,
AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
M_Product_ID, MovementQty, M_Locator_ID)
VALUES
(NextNo, pp.M_ProductionPlan_ID, Line,
pp.AD_Client_ID,pp.AD_Org_ID,'Y',CURRENT_DATE,0,CURRENT_DATE,0,
bom.M_ProductBOM_ID, pl.MovementQty*bom.BOMQty, pp.M_Locator_ID);
END LOOP;
-- Delete BOM line
DELETE M_ProductionLine
WHERE M_ProductionLine_ID=pl.M_ProductionLine_ID;
END LOOP;
END LOOP; -- While we have BOMs
END LOOP; -- For every Production Plan
-- Modifying locator to have sufficient stock

-- Indicate that it is Created
UPDATE M_Production
SET IsCreated='Y'
WHERE M_Production_ID=Record_ID;
/**************************************************************************
* Post Lines
*/
ELSE
-- All Production Lines
FOR pl IN SELECT pl.M_ProductionLine_ID, pl.AD_Client_ID, pl.AD_Org_ID, p.MovementDate,
pl.M_Product_ID, pl.M_AttributeSetInstance_ID, pl.MovementQty, pl.M_Locator_ID
FROM M_Production p, M_ProductionLine pl, M_ProductionPlan pp
WHERE p.M_Production_ID=pp.M_Production_ID
AND pp.M_ProductionPlan_ID=pl.M_ProductionPlan_ID
AND pp.M_Production_ID=Record_ID
ORDER BY pp.Line, pl.Line LOOP
IF (pl.MovementQty < 0 AND MustBeStocked <> 'N'
AND bomQtyOnHand(pl.M_Product_ID, NULL, pl.M_Locator_ID)+pl.MovementQty < 0) THEN
ROLLBACK;
SELECT '@NotEnoughStocked@: ' || Name INTO Message
FROM M_Product WHERE M_Product_ID=pl.M_Product_ID;
GOTO FINISH_PROCESS;
END IF;
-- Adjust Quantity at Location
UPDATE M_Storage
SET QtyOnHand = QtyOnHand + pl.MovementQty,
Updated = CURRENT_DATE
WHERE M_Locator_ID = pl.M_Locator_ID
AND M_AttributeSetInstance_ID = COALESCE(pl.M_AttributeSetInstance_ID,0)
AND M_Product_ID = pl.M_Product_ID;
-- Product not on Stock yet
IF (SQL%ROWCOUNT = 0) THEN
INSERT INTO M_Storage
(M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
QtyOnHand, QtyReserved, QtyOrdered)
VALUES
(pl.M_Product_ID, pl.M_Locator_ID, COALESCE(pl.M_AttributeSetInstance_ID,0),
pl.AD_Client_ID, pl.AD_Org_ID, 'Y', CURRENT_DATE, 0, CURRENT_DATE, 0,
pl.MovementQty, 0, 0);
END IF;

-- Create Transaction Entry
ResultStr := 'CreateTransaction';
AD_Sequence_Next('M_Transaction', pl.AD_Org_ID, NextNo);
INSERT INTO M_Transaction
(M_Transaction_ID, M_ProductionLine_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
MovementType, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID,
MovementDate, MovementQty)
VALUES
(NextNo, pl.M_ProductionLine_ID,
pl.AD_Client_ID, pl.AD_Org_ID, 'Y', CURRENT_DATE, 0, CURRENT_DATE, 0,
'P+', pl.M_Locator_ID, pl.M_Product_ID, COALESCE(pl.M_AttributeSetInstance_ID,0), -- not distinguishing between assemby/disassembly
pl.MovementDate, pl.MovementQty);
--
UPDATE M_ProductionLine
SET Processed='Y'
WHERE M_ProductionLine_ID=pl.M_ProductionLine_ID;
END LOOP;
-- Indicate that we are done
UPDATE M_Production
SET Processed='Y'
WHERE M_Production_ID=Record_ID;
UPDATE M_ProductionPlan
SET Processed='Y'
WHERE M_Production_ID=Record_ID;
END IF;

RAISE NOTICE 'Entire job successful';
--<<FINISH_PROCESS>>
-- Update AD_PInstance
DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || Message);
UPDATE AD_PInstance
SET Updated = CURRENT_DATE,
IsProcessing = 'N',
Result = 1, -- success
ErrorMsg = Message
WHERE AD_PInstance_ID=PInstance_ID;
RAISE NOTICE 'Updated AD_PIntance';
RETURN;
EXCEPTION
WHEN OTHERS THEN
ResultStr := ResultStr || ': ' || 'SQLERRM' || ' - ' || Message;
-- DBMS_OUTPUT.PUT_LINE(ResultStr);
UPDATE AD_PInstance
SET Updated = CURRENT_DATE,
IsProcessing = 'N',
Result = 0, -- failure
ErrorMsg = ResultStr
WHERE AD_PInstance_ID=PInstance_ID;
RAISE NOTICE 'Exception';
RETURN;
END ;
$$ LANGUAGE plpgsql;

Ya he logrado transformar varios procedimientos almacenados y esos funcionan a la perfección. Para lo cual si he leído la documentación tanto de postgresql 7 como del 8 que es este ultimo en el que me encuentro trabajando hoy. Pero para este caso no he encontrado una solución por eso decidí consultar si alguien podía ayudarme

Bueno, gracias de todas maneras

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> escribió:
On Thu, Jul 28, 2005 at 10:54:00AM -0500, Jaime Casanova wrote:
> On 7/28/05, Marco Villagrán wrote:
> > Acá mando el procedimiento que es de oracle
> >
> > CREATE OR REPLACE PROCEDURE M_Production_Run
>
> De hecho, creo que lo que alvaro queria ver es el procedimiento como
> los has hecho en plpgsql...

Efectivamente.

> BTW, en plpgsql no puedes usar commit ni rollback... solo por si acaso...

De hecho no se puede. Una recomendacion antes de intentar "portar" el
procedimiento es realmente leer la documentacion relevante. (TODA la
seccion de plpgsql como minimo).

--
Alvaro Herrera ()
"[PostgreSQL] is a great group; in my opinion it is THE best open source
development communities in existence anywhere." (Lamar Owen)

__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
Regístrate ya - http://correo.espanol.yahoo.com/