the cursor for loop in postgresql

Question

We have a function written in pl/sql(oracle) as below:

CREATE OR REPLACE PROCEDURE folder_cycle_check (folder_key IN NUMBER, new_parent_folder_key IN NUMBER) IS
    parent_of_parent NUMBER;
    ILLEGAL_CYCLE EXCEPTION;
    CURSOR parent_c IS
    SELECT parent_folder_key FROM folder
        WHERE folder_key = new_parent_folder_key;
BEGIN

IF folder_key = new_parent_folder_key THEN
    RAISE ILLEGAL_CYCLE;
END IF;

FOR parent_rec IN parent_c LOOP
    BEGIN folder_cycle_check(folder_key, parent_rec.parent_folder_key); END;
END LOOP;

END;

Now, i have to rewrite this same procedure in pl/pgsql(PostgreSQL) to achieve similar functionality. Please help me and send that pl/pgsql function.

Edit (formatted code from the comments)

CREATE OR REPLACE FUNCTION folder_cycle_check(IN folder_key INTEGER, IN new_parent_folder_key INTEGER) 
  RETURNS VOID 
AS $procedure$ 
   DECLARE parent_of_parent INTEGER; 
   PARENT_C CURSOR FOR 
        SELECT parent_folder_key 
        FROM folder 
        WHERE folder_key = new_parent_folder_key; 
BEGIN 
    IF folder_key = new_parent_folder_key THEN 
        RAISE EXCEPTION 'ILLEGAL_CYCLE'; 
    END IF

    FOR parent_rec IN (SELECT parent_folder_key FROM folder WHERE folder_key = new_parent_folder_key) LOOP 
        PERFORM folder_cycle_check(folder_key,parent_rec.parent_folder_key); 
    END LOOP; 

    RETURN; 
END; 
$procedure$ 
LANGUAGE plpgsql;

Do you have any compiler errors with your Postgres solution? If yes, please post them together with your Postgres code — user330315
– user330315, Commented Jul 4, 2011 at 12:52
What is your attempt. Remember, this is not a "please do my job for me" site. You must show some attempt - we don't spoon feed here. — Bohemian
– Bohemian ♦, Commented Jul 4, 2011 at 12:55
Thanks bohemian for the kind reply. This is my attempt. CREATE OR REPLACE FUNCTION folder_cycle_check(IN folder_key INTEGER, IN new_parent_folder_key INTEGER) RETURNS VOID AS $procedure$ DECLARE parent_of_parent INTEGER; PARENT_C CURSOR FOR SELECT parent_folder_key FROM folder WHERE folder_key = new_parent_folder_key; BEGIN IF folder_key = new_parent_folder_key THEN RAISE EXCEPTION 'ILLEGAL_CYCLE'; END IF — vchitta
– vchitta, Commented Jul 4, 2011 at 13:48
FOR parent_rec IN(SELECT parent_folder_key FROM folder WHERE folder_key = new_parent_folder_key) LOOP PERFORM folder_cycle_check(folder_key,parent_rec.parent_folder_key); END LOOP; RETURN; END; $procedure$ LANGUAGE plpgsql; — vchitta
– vchitta, Commented Jul 4, 2011 at 13:49
i am suspecting that i am not at all going into the for loop. I placed a debug statement inside the for loop as RAISE NOTICE or RAISE EXCEPTION 'Hi' but is also not caught. basic purpose of this function is to check if a parent folder is copied (assigned as child) to its own child. If so, raise exception. Am i wrong in using FOR LOOP with CURSORS? suggest — vchitta
– vchitta, Commented Jul 4, 2011 at 13:54

score 16 · Accepted Answer · 2011-07-05 10:04:07Z

16

This should work:

CREATE OR REPLACE FUNCTION folder_cycle_check (p_folder_key INT4, p_new_parent_folder_key INT4) RETURNS VOID AS $$
DECLARE
    v_parent_rec RECORD;
BEGIN
    IF folder_key = new_parent_folder_key THEN
        RAISE EXCEPTION 'ILLEGAL_CYCLE';
    END IF;
    FOR v_parent_rec IN SELECT parent_folder_key FROM folder WHERE folder_key = p_new_parent_folder_key LOOP
        PERFORM folder_cycle_check(folder_key, v_parent_rec.parent_folder_key)
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

edited Jul 5, 2011 at 10:04

answered Jul 4, 2011 at 14:51

user80168

Sign up to request clarification or add additional context in comments.

1 Comment

vchitta Over a year ago

but we should use FUNCTION in place of PROCEDURE for postgresql.

Collectives™ on Stack Overflow

the cursor for loop in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related