postgresql 中的游标循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6571754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 22:59:58 来源:igfitidea点击:
the cursor for loop in postgresql
提问by vchitta
We have a function written in pl/sql(oracle) as below:
我们有一个用 pl/sql(oracle) 编写的函数，如下所示：
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.
现在，我必须在 pl/pgsql(PostgreSQL) 中重写这个相同的过程来实现类似的功能。请帮助我并发送该 pl/pgsql 函数。
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;
回答by
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;
