Friday, April 7, 2023

Oracle row type vs PostgreSQL record

The Oracle row type concept can be accomplished with the record type in PostgreSQL.

Example:

Oracle:

CURSOR travel_cur IS 
SELECT  c.travel_id FROM TRAVEL_CON c, TRAVEL_PAT p
                                WHERE c.travel_id = p.travel_id AND p.pat_id = source;
travel_rec travel_cur%ROWTYPE;         <---- here travel_rec holds multiple records at a time.
BEGIN
OPEN travel_cur; 
    LOOP 
        FETCH travel_cur INTO travel_rec; 
        EXIT WHEN travel_cur%NOTFOUND; 
        sql_stmt := 'delete from TRAVEL_PAT where travel_id = :1'; 
        EXECUTE IMMEDIATE sql_stmt USING travel_rec.travel_id;
    END LOOP; 
CLOSE travel_cur;
END;

PostgreSQL:

DECLARE
    sql_stmt varchar(1000);
    travel_rec record;            <---- here travel_rec holds only a single record at a time.
BEGIN
    FOR travel_rec  in SELECT  c.travel_id FROM TRAVEL_CON c, TRAVEL_PAT p
                                WHERE c.travel_id = p.travel_id AND p.pat_id = source;
    LOOP
            sql_stmt := 'delete from TRAVEL_PAT where travel_id = :1'; 
    EXECUTE IMMEDIATE sql_stmt USING travel_rec.travel_id;
    END LOOP; 
END;

No comments:

Post a Comment

Oracle row type vs PostgreSQL record

The Oracle row type concept can be accomplished with the record type in PostgreSQL. Example: Oracle: CURSOR travel_cur IS  SELECT  c.travel_...