the cursor for loop in postgresql
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_k开发者_开发问答ey); 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;
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;
精彩评论