We are migrating from Oracle to Postgres. I have the next code. I am stuck with bulk collect cursor. I tried using insert into .. select * from.. But as this code check with some particular column, I am confused.
CREATE OR REPLACE FUNCTION usmqa.pk_vpdata_load_load_vdrarmhlink( OUT err_code bigint, OUT vdrarmhlink_errmsg text) RETURNS record LANGUAGE 'plpgsql'
AS $BODY$ DECLARE
V_COUNT_VDRARMHLINK bigint;
CUR_GET_INT_VDRARMHLINK CURSOR FOR
SELECT VENLINKID,
VENARINTNO,
VENMVINTNO,
VENLINKEFFDT,
VENLINKTRMDT,
VENACT,
VENRUNSTS,
VENPROCID,
VENERRCODE
FROM INT_VDRARMHLINK
WHERE VENRUNSTS = -1
AND coalesce(VENERRMSG::text, '') = ''
AND coalesce(VENERRCODE::text, '') = '';
-- TYPE TYP_GET_INT_VDRARMHLINK IS TABLE OF RECORD INDEX BY integer; Oracle code
--REC_GET_INT_VDRARMHLINK TYP_GET_INT_VDRARMHLINK; --- Oracle
REC_GET_INT_VDRARMHLINK int[];
I int;
BEGIN
glo_error_var:= 0;
UPDATE INT_VDRARMHLINK SET VENRUNSTS = -1 WHERE VENRUNSTS = 0;
glo_error_var:= 1;
UPDATE INT_VDRARMHLINK
SET VENERRMSG = 'VENDOR INTERNAL NUMBER DOES NOT EXISTS VDREXT TABLE',
VENERRCODE = -1,
VENRUNSTS = -2
WHERE NOT EXISTS (SELECT 1 FROM VDREXT WHERE VDXINTNO = VENARINTNO)
OR NOT EXISTS (SELECT 1 FROM VDREXT WHERE VDXINTNO = VENMVINTNO);
COMMIT;
glo_error_var:= 2;
OPEN CUR_GET_INT_VDRARMHLINK;
LOOP
-- FETCH CUR_GET_INT_VDRARMHLINK BULK COLLECT ---- Oracle code
-- INTO REC_GET_INT_VDRARMHLINK LIMIT 1000; ---- Oracle code
-- FOR I IN 1 .. REC_GET_INT_VDRARMHLINK.COUNT LOOP -- Oracle code
SELECT arrat_agg(CUR_GET_INT_VDRARMHLINK)
into REC_GET_INT_VDRARMHLINK
FROM CUR_GET_INT_VDRARMHLINK;
FOREACH I SLICE 1 in ARRAY REC_GET_INT_VDRARMHLINK
LOOP
BEGIN
SELECT COUNT(1)
INTO STRICT V_COUNT_VDRARMHLINK
FROM VDRARMHLINK
WHERE VENARINTNO = REC_GET_INT_VDRARMHLINK[I].VENARINTNO
AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I].VENMVINTNO;
END;
--INSERT
IF ( V_COUNT_VDRARMHLINK = 0 AND
REC_GET_INT_VDRARMHLINK[I].VENRUNSTS = -1) THEN
SELECT VENLINKID,
VENARINTNO,
VENMVINTNO,
VENLINKEFFDT,
VENLINKTRMDT,
VENACT,
VENRUNSTS,
VENPROCID,
VENERRCODE
FROM INT_VDRARMHLINK
WHERE VENRUNSTS = -1
AND coalesce(VENERRMSG::text, '') = ''
AND coalesce(VENERRCODE::text, '') = '';
BEGIN
INSERT INTO VDRARMHLINK
(VENLINKID,
VENARINTNO,
VENMVINTNO,
VENLINKEFFDT,
VENLINKTRMDT)
VALUES (REC_GET_INT_VDRARMHLINK[I].VENLINKID,
REC_GET_INT_VDRARMHLINK[I].VENARINTNO,
REC_GET_INT_VDRARMHLINK[I].VENMVINTNO,
REC_GET_INT_VDRARMHLINK[I].VENLINKEFFDT,
REC_GET_INT_VDRARMHLINK[I].VENLINKTRMDT);
UPDATE INT_VDRARMHLINK
SET VENRUNSTS = 1
WHERE
VENPROCID = REC_GET_INT_VDRARMHLINK[I].VENPROCID
AND VENERRCODE = NULL
AND VENERRMSG = NULL;
END;
END IF;
IF (V_COUNT_VDRARMHLINK = 1) THEN
BEGIN
UPDATE VDRARMHLINK
SET VENLINKEFFDT = REC_GET_INT_VDRARMHLINK[I].VENLINKEFFDT,
VENLINKTRMDT = REC_GET_INT_VDRARMHLINK[I].VENLINKTRMDT
WHERE VENLINKID = REC_GET_INT_VDRARMHLINK[I].VENLINKID
AND VENARINTNO = REC_GET_INT_VDRARMHLINK[I].VENARINTNO
AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I].VENMVINTNO;
UPDATE INT_VDRARMHLINK
SET VENRUNSTS = 1
WHERE VENPROCID = REC_GET_INT_VDRARMHLINK[I]
.VENPROCID
AND VENLINKID = REC_GET_INT_VDRARMHLINK[I]
.VENLINKID
AND VENARINTNO = REC_GET_INT_VDRARMHLINK[I]
.VENARINTNO
AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I]
.VENMVINTNO
AND VENERRCODE = NULL
AND VENERRMSG = NULL;
END;
END IF;
--DELETE
IF (REC_GET_INT_VDRARMHLINK[I].VENACT = 2 AND V_COUNT_VDRARMHLINK = 1) THEN
BEGIN
UPDATE VDRARMHLINK
SET VENLINKEFFDT = REC_GET_INT_VDRARMHLINK[I].VENLINKEFFDT,
VENLINKTRMDT = clock_timestamp()
WHERE VENLINKID = REC_GET_INT_VDRARMHLINK[I]
.VENLINKID
AND VENARINTNO = REC_GET_INT_VDRARMHLINK[I]
.VENARINTNO
AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I].VENMVINTNO;
UPDATE INT_VDRARMHLINK
SET VENRUNSTS = 1
WHERE VENACT = 2
AND VENPROCID = REC_GET_INT_VDRARMHLINK[I]
.VENPROCID
AND VENLINKID = REC_GET_INT_VDRARMHLINK[I]
.VENLINKID
AND VENARINTNO = REC_GET_INT_VDRARMHLINK[I]
.VENARINTNO
AND VENMVINTNO = REC_GET_INT_VDRARMHLINK[I]
.VENMVINTNO
AND VENERRCODE = NULL
AND VENERRMSG = NULL;
ELSIF (REC_GET_INT_VDRARMHLINK[I]
.VENACT = 2 AND V_COUNT_VDRARMHLINK = 0) THEN
UPDATE INT_VDRARMHLINK
SET VENRUNSTS = -3,
VENERRCODE = -300,
VENERRMSG = 'THERE IS NO VENODR AR MERCHANDISE IN VP TO DELETE'
WHERE VENACT = 2
AND VENPROCID = REC_GET_INT_VDRARMHLINK[I]
.VENPROCID
AND VENERRCODE = NULL
AND VENERRMSG = NULL;
END IF;
END LOOP;
EXIT WHEN NOT FOUND; /* apply on CUR_GET_INT_VDRARMHLINK */
END LOOP;
CLOSE CUR_GET_INT_VDRARMHLINK;
END;
$BODY$;
